Forum Discussion
Measures Total Aggregation
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.
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?
Community SupportHi 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.
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!