topic Re: Measure not working as expected in hierarchy in DAX Commands and Tips

Measure not working as expected in hierarchy

Anonymous — Tue, 24 Jan 2023 14:14:46 GMT

Hello,

I have the following example data:

Model	Submodel	Sold units	demand forecast	ABS(sold units-demand)
x	x1	3	2	1
x	x2	4	7	3
x	x3	15	4	11
x	x4	6	4	2

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:

Model	Sold units	Demand Forecast	sum of Abs
x	28	17	17

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

Thanks in advance

Re: Measure not working as expected in hierarchy

Greg_Deckler — Tue, 24 Jan 2023 14:22:15 GMT

Anonymous 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

Re: Measure not working as expected in hierarchy

FreemanZ — Tue, 24 Jan 2023 14:27:23 GMT

hi Anonymous

try to create a measure like this:

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

it worked like this:

Re: Measure not working as expected in hierarchy

Anonymous — Tue, 24 Jan 2023 14:30:30 GMT

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.).

Re: Measure not working as expected in hierarchy

Anonymous — Tue, 24 Jan 2023 14:31:19 GMT

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