cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

)

1 ACCEPTED SOLUTION

Accepted Solutions

Try something like this for the total:

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


Br,
J


Connect on LinkedIn

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

share sample data and expect output

CategoryValue1Value2Measure (Value1/Value2)
Cat117784702179768.16

Cat2

249663413229418.87
Total427510435027024.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


Connect on LinkedIn

View solution in original post

Super User II
Super User II

@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

result.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






It works, thank you all!

 

Thanks buddy, this resolved a big issue for me.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors