Different Measure Total using SUMX

[mafaber]

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])))

)

[PaulDBrown]

@mafaber 

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

[Anonymous]

Thanks buddy, this resolved a big issue for me.

[Anonymous]

share sample data and expect output

[mafaber]

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

[tex628 · Accepted solution]

Try something like this for the total:

Measure2 = 
SUMX(
SUMMARIZE(
'Table';
'Table'[Category];
"Value" ; [Measure]
) ;
[Value]
)

Br,
J

[mafaber]

It works, thank you all!