Forum Discussion
Measures Total Aggregation
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!
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.
4 Replies
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/547907Would need sample data to be more specific.
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?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.
