Forum Discussion
Divide through Total not row ccontex wise
Hello,
for example I want the total amount divided through the needed to get the Column "Ratio", how to I get the total values
| Object | Pos.No | Amount | Needed | Ratio | RatioWrong |
| BAB1 | 600 | 3 | 100 | 0,16 | 0,04 |
| BAB1 | 610 | 13 | 100 | 0,16 | 0,19 |
| BAB2 | 800 | 5 | 200 | 0,06 | 0,0125 |
| BAB2 | 810 | 7 | 200 | 0,06 | 0,035 |
sum('Table1'[Amount]) / sum('Table2'[Needed]) always is in row context. The total amount of the object BAB1 for example is 16. I want to get out of the row context, to get the Total Ratio of that Object divided through one time of "Needed". Thats the reason why I get "RatioWrong" instead.
For Object BAB2 it should be 5+7 divided throgh 200, which equals 0,06. I tried a caluclate with the "All-Filter", but that doesn't differentiate between the different Objects.
Thank you very much in advance.
Best.
Hi Applicable88,
Try measure as:
Measure = CALCULATE( DIVIDE( SUMX(FILTER(ALL('Table'),'Table'[Object]=MAX('Table'[Object])),'Table'[Amount]), CALCULATE(MAX('Table'[Needed]))))Here is the output:
The demo is attached, please try it.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
4 Replies
maybe try
calculate(sum('Table1'[Amount]),allexcept(sum('Table1','Table1'[Object]))/ sum('Table2'[Needed])
Hello ryan_mayu , that isn't working. The syntax is not right. But I deleted the first argument of allexcept. Because I guess the double "sum" is one too much there.
But now it sums up all the "needed" amount and that one should only be divided one time. For Object BAB1 it should be 16/100 oder for BAB2 it should be 12/200. Have any idea how to get there?
maybe separate the DAX and test for each part
check if below returns 16 for BAB1 and 12 for BAB2
calculate(sum('Table1'[Amount]),allexcept(sum('Table1','Table1'[Object]))
if you can provide the pbix file , that will be easier for us to provide the proper solution.
Hi Applicable88,
Try measure as:
Measure = CALCULATE( DIVIDE( SUMX(FILTER(ALL('Table'),'Table'[Object]=MAX('Table'[Object])),'Table'[Amount]), CALCULATE(MAX('Table'[Needed]))))Here is the output:
The demo is attached, please try it.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
