Measures Total Aggregation

[Anonymous]

Hello,

I need support please to prevent Measures from aggregating when summing the total. To simplify, consider the below example where each of the below is stored in a different PowerBI table and hence SUMX hasn't been helping me:

Products	Total Due Volume	Total Stocks	Missing Volume
Product A	10	0	10
Product B	5	0	5
Product C	0	90	0
Measure PowerBI Total	15	90	0
Correct Total	15	90	15

Given data is scattered across several tables in my model (and I cant really append them together), I used a simple IF ( Total Due Volume - Stocks > 0, 0, Total Due Volume - Stocks). The calculation works perfectly on product or line item level yet when PowerBI aggregates the total (either in tables or graphs), for the total specifically it looks at the total volume - total stocks which always gives the 0 (in bold above) since there is one outlier - product C in this case which has too many stocks- while what I am trying to achieve is make ti sum only the rows where missing volume is > 0 (equivalent of a sum if in excel) to get a sum of 15. Can you please let me know how?

Note: Another workaround can be doing this as a new column in the table that houses the products instead yet I am opting for a measure because I need it to interact with a GenerateSeries slicer which displays different results as you move it up and down, something that doesn't work with tables unfortunately.

Many thanks in advance!

[Greg_Deckler]

@Anonymous 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

Would need sample data to be more specific.

[Anonymous]

Many thanks @Greg_Deckler, highly appreciated! The proposed solution definitely got me one step closer now but I still need help please in writing the equation for the total calculation standalone itself.

Has one value works perfectly if all data is in one table but in my simplified example above, the 3 columns come from different tables and each require some filtering to get these numbers so how would I calculate the total in this case? Would it be something along the lines of creating a var table within my measure?

[v-xiaotang · Accepted solution]

Hi @Anonymous 

The reason for this error is obviously because the logic for calculating the above rows is still called when calculating the total row.

-

"the 3 columns come from different tables and each require some filtering to get these numbers "

in this scenario, you can try the way below, and since it is not clear what key is used to connect your product table with other tables, I will first assume that they are related with product name. Of course, if it is another key, you can replace it later, the principle is the same.

-

create the measures

(1) how to change [Total Due Volume] [Total Stocks]: 

Total Due Volume = 
var _eachrow= SUMX(FILTER(ALL(Due),Due[Products]=MIN(ProductsList[Products])), Due[Due Volume])
var _totalrow= SUMX(ALL(Due),Due[Due Volume])
return IF(HASONEVALUE(ProductsList[Products]),_eachrow,_totalrow)

Total Stocks = 
var _eachrow= SUMX(FILTER(ALL(Stocks),Stocks[Products]=MIN(ProductsList[Products])), Stocks[Stocks])
var _totalrow= SUMX(ALL(Stocks),Stocks[Stocks])
return IF(HASONEVALUE(ProductsList[Products]),_eachrow,_totalrow)

(2) how to change [Missing Volume]: 

Missing Volume 1 = IF ( [Total Due Volume] - [Total Stocks] < 0, 0, [Total Due Volume] - [Total Stocks] )

Missing Volume 2 = SUMX(ProductsList,[Missing Volume 1])

then put Missing Volume 2 into the visual.

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for the effort! It finally works after trying many things for 7 months!

Note: For some reason the total due volume and stocks calculation didn't work (but it's ok since I have the right calculation for it anyway), but what matters is that it turned out to be as simple as creating a dummy measure then using a SUMX with this dummy measure on my products list!