cancel
Showing results for
Did you mean:
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
Super User II

Try something like this for the total:

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

Br,
J

6 REPLIES 6
Anonymous
Not applicable

share sample data and expect output

Helper I
 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

Super User II

Try something like this for the total:

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

Br,
J

Super User II

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

It works, thank you all!

New Member
Thanks buddy, this resolved a big issue for me.

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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