Forum Discussion
Granularity Issue
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!
3 Replies
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/547907Greg_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
