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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
orderstatus_shi
Regular Visitor

Measure not working as expected in hierarchy

Hello,

 

I have the following example data:

 

ModelSubmodelSold unitsdemand forecastABS(sold units-demand)
xx1321
xx2473
xx315411
xx4642

 

I would like now to sum the different abs values (0+1+1+2) in model granularity, but instead, when applying to a Matrix, it calculates the abs as the following:

(3+4+15+6) - (2+7+4+4) = 28-17 =11

While I would like it to sum it like: (1+3+11+2) = 17

So the table would be:

ModelSold unitsDemand Forecastsum of Abs
x281717

 

I have been trying everything and searching around. What is the proper way of doing this, and understanding it?

 

Thanks in advance

4 REPLIES 4
FreemanZ
Super User
Super User

hi @orderstatus_shi 

try to create a measure like this:

AccuracyABS = 
SUMX(
    TableName,
    ABS(TableName[Sold units] - TableName[demand forecast])
)

it worked like this:

FreemanZ_0-1674570427447.png

 

Thanks for your answer.
The problem is Sold units and Demand Forecast are two different fact tables (Orders and Forecast), linked to a parent table called "Master" where there are categories to filter by (model, SKU, etc.).

Greg_Deckler
Super User
Super User

@orderstatus_shi First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

 

Also, see this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg, tysm for your answer.
I have definately seen that different times but never started to learn about it. Thanks for the resources.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors