topic Re: Granularity Issue in DAX Commands and Tips

Granularity Issue

Anonymous — Thu, 16 Apr 2020 14:10:12 GMT

I think I have a granularity issue with a Measure, where the Measure works fine at a more granular level, but when I try to roll-up my numbers, while it appears the Measure is working, the value is incorrect.

Source files can be found here.

The issue is with the Measure [Prog Area Gross] (found under the table 'prog_SiteProgram' because it is closely related).

On the Tab Site2.1 the value of the Prog Area Gross column should be 1.5 ([Grossup]) multiplied by the column [Program Area]. In the upper table the match is correct, if you look at the lower table for the Row Acute Care you'll see the answer is incorrect. I think the issue may trace back to how the Measure [Program Area] is being calculated. I think when the lower table rolls up the ServiceName "Acute Care" because the way all of the related measures are working either area is not being summed correclty or I have area that is being multipilied by the wrong percentage (in [Program Area]).

For reference it may be worth reviewing a previous post of mine that gives further back story on the underlying data model.

Thanks for looking!

Re: Granularity Issue

Greg_Deckler — Thu, 16 Apr 2020 15:13:11 GMT

Hmm, probably this:

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Or possibly this:

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Re: Granularity Issue

Anonymous — Thu, 16 Apr 2020 15:36:24 GMT

Thank you for the links, indeed this seems to be going down the road I suspected, will review in detail.

Regards,

-Robert

Re: Granularity Issue

Anonymous — Mon, 20 Apr 2020 23:04:50 GMT

Greg_Deckler thank you for the links. I've read through the first two twice (maybe three times) and I have it "working".

I also implemented a combined measure using the HASONEFILTER technique. However, I have an intermediate grouping where I don't get the expected results. I have not yet fully read the "Triple Threat" link (skimmed it). I'm thinking I need to dive back into that in order to deal with various levels of aggregation?

Thanks,

-R