Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

Anonymous
Not applicable

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

 

Regards,

-Robert

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors