Different Matrix Values by Level

daniel.new · December 2, 2021, 3:05pm

Me again… similar to my last post, but different…

I have multiple datasets/tables pulling in revenue numbers each month. My matrix hierarchy consists of the following levels in this order:

–>State
–>Warehouse Location
–>Client Corporate Name
–>Client Location

I have several filters/slicers on the page to allow filtering down to a specific Warehouse Location, Client Corporate or Location, and Region (not a level in the matrix, but filter only).

Most of the time in a matrix if you filter down via slicer to the most granular level, the values returned in the matrix for that selection repeat down the same values, such as the column in my attached screenshot for “DME Days”.

What I Want: I want my Revenue to maintain and show the total respective values at each rollup/level in the hierarchy (as shown in my screenshot). HOWEVER, I only want the Warehouse Locations and States to show respective to the values I’ve selected in the slicer. You can see from my screenshot how Revenue is showing for all State and Warehouse levels, not just for the group associated with the Client Location I have selected in my slicer.

*I’ve also included a screenshot of the DAX I used to make the revenue show the correct values by level, but obviously I’m missing something. Can you someone take a look and see what’s going on? It could very well be that I used completely incorrect functions, but it was the only thing I could remotely get to work at all. I’ve looked at other functions like HasOneValue, RollUp/Summarize, IsFiltered, etc. but still no luck.

*One last problem. My measure for Revenue is being used in other pages of my report, including a summary page with 6 different line graphs. Ever since I modified the Revenue measure with the attached code, the line graphs with Revenue calculations show completely blank graphs.

I know this was a ton of information, so let me know if you need clarification or additional information. Unfortunately, I can’t send the PBIX file due to proprietary information.

Thank you!!
Daniel

Anurag · December 3, 2021, 9:05am

Hi,

Pls provide your work in progress pbix so we can look into it.

Thanks,
Anurag

daniel.new · December 3, 2021, 3:41pm

I’m not able to provide the PBIX file due to confidential/proprietary information within my organization. However, if there is additional DAX code that would be helpful, in addition to what I’ve already provided, I’m happy to provide.

EnterpriseDNA · December 6, 2021, 10:42pm

Hello @daniel.new, here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:

daniel.new · December 7, 2021, 3:05pm

Hi Everyone,

Just bumping this up again. Does anyone by chance have thoughts as to what’s going on with my matrix and how to fix?

Thank you!
Daniel

Keith · December 7, 2021, 9:28pm

my thoughts that no one is looking at it because you didn’t supply a powerbi file and expect results with dummy data in so that can view your model. If you review the video that (how to mask sensitive data)
that will help you to put the pbi file. You might have your model setup wrong and your dax formulas aren’t working the way it should,

thanks

daniel.new · December 8, 2021, 12:37am

Thanks for the feedback.

BrianJ · December 8, 2021, 1:40am

@daniel.new ,

Just to provide a little more perspective – the reason we are so insistent on a PBIX file is two-fold:

Very frequently, we will get questions on the forum along the lines of “why does this DAX not work?” Commonly, we will get questions on the forum along the lines of “why does this DAX not work?” Very frequently it turns out that the DAX is spot-on, but that the data model is broken and thus not producing accurate results even from proper DAX.
I think I’m pretty good with DAX, but once DAX gets beyond a certain point of complexity I absolutely can’t troubleshoot it just by looking at it, and need to have both the data model and the data itself to play around with to generate a solution.

Here’s a post I put together a while ago that provides three different strategies and associated tools for generating non-confidential data that sufficiently approximates your actual data and model to allow us to diagnose the problem and provide you a solution.

https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

I hope this is helpful.

Brian

EnterpriseDNA · December 10, 2021, 12:04am

Hello @daniel.new we noticed that no response has been received from you.

We are waiting for your masked demo pbix file and other supporting links and details. Other users can help you more if you have a PBIX file in your query as suggested above.

In case there won’t be any activity from you in the coming days, we’ll beclosing this thread.