Forum Discussion
Different Measure Total using SUMX
Hi,
I would like to change how my measure calculates the total. For that I'm using the HASONEFILTER function and that works just fine, it correctly evaluates the first expression for each value row and total expression for the total row, but for the total row I just can't get the result right. Basically what I'd like to do is instead of evaluating the expression on the totals, I want to see the expression evaluated for each row and then summed up. To my understanding that is what SUMX is for, but it is somehow not correct.
What am I doing wrong?
Measure_test =
switch(true(),
hasonefilter(master_customer_database[Market]),
divide(sum(table1[column1]), sum(table1[column2])),
sumx(table1, divide(sum(table1[column1]), sum(table1[column2])))
)
Try something like this for the total:
Measure2 = SUMX( SUMMARIZE( 'Table'; 'Table'[Category]; "Value" ; [Measure] ) ; [Value] )
Br,
J
6 Replies
Try the following (I'm using random data):
1) the measures:
Sum of Actuals = SUM('Data Table'[Actuals])Sum of Target = SUM('Data Table'[Target])Actuals / Target = DIVIDE([Sum of Actuals]; [Sum of Target])SUMX of Actuals / Target = SUMX(SUMMARIZE('Data Table'; 'Data Table'[Channel]; "Calculation"; [Actuals / Target]); [Calculation])And you will get this result
Category Value1 Value2 Measure (Value1/Value2) Cat1 1778470 217976 8.16 Cat2
2496634 132294 18.87 Total 4275104 350270 24.41 So as you can see Measure is Value1 / Value 2 for each row. The Total Value 1 and Total Value 2 is also correct.
I noticed now that the 24.41 is the double of what the Total Value1 / Total Value2 would be (12.205). But I don't see why.
Expected result would be 8.16+18.87=27.03
Try something like this for the total:
Measure2 = SUMX( SUMMARIZE( 'Table'; 'Table'[Category]; "Value" ; [Measure] ) ; [Value] )
Br,
J
