Divide through Total not row ccontex wise

[Applicable88]

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. 

[v-xulin-mstf · Accepted 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!

[ryan_mayu]

@Applicable88 

maybe try

calculate(sum('Table1'[Amount]),allexcept(sum('Table1','Table1'[Object]))/ sum('Table2'[Needed])

[Applicable88]

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? 

[ryan_mayu]

@Applicable88 

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.