cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Total Sum of a measure

I have this table with a measure calculated based on data 1, 2 and 3.

What I need to get is the total sum of the measure, however as you can see, the sum of my measure collumn is 39 and not the actual sum of the collumn.

This is because instead of just summing up the total, powerbi instead applies the same measure on the totals line.

Is there a way to either set the collumn to calculating the sum only? Is there a way to sum it using another measure somehow? Should I just go back to using excel?

1 ACCEPTED SOLUTION
Super User

Take a look at my tip here:

http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

Seems like you are running into this problem.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Anonymous
Not applicable

Hi,

had the same issue and solved it this way:

test total =
SUMX(
SUMMARIZE(
'Write OFFs db 19',stores[Store name],
"t",
calculate(
sum(
'Write OFFs db 19'[Write OFFs Cost Loss]
),
FILTER(
'Write OFFs db 19',
'Write OFFs db 19'[Period]>=max(
'Stocktakes dates FY19'[FY19_first_count]
)
)
)
),
[t]*1
)

What you need to do is create virtual table using "summarize", then on this virtual table perform "sumx" *1.

This will still show you breakdown and correct total at the end.

daniel

Regular Visitor

Hello Guys,

i think i have a very similar problem as discussed above. My try to use the given Solution would not help. So maybe anyone of the experts in this community can help me.

I have 4 Colums as followed:

 Month Actual PLAN Divergence September 2.185.572 780.000 1.405.572 Oktober 543.343 1.760.000 -1.216.657 November 0 3.663.000 0 ... Total 2.728.915 6.203.000 -3.474.085

What this table does.
I have the first column, which gives the months.
The second column, shows me my actual revenue.

The third column, shows me my planned revenue for given month.

The fourth column, shows the divergence for given month. (It only calculate if my acutal revenue is > 0 for a better overview)

What i want to see at the total row in column "divergence" is the SUM of the values shown in column "divergence" and not the divergence of the total row.

So actual i see the value  -3.474.085. What i want to see is the value 188.915.

I tried the hole day to find out a solution for it, without success.
Can somebody help me pleaase?

Best Regards und and many thanks in advance!

Frequent Visitor

I believe what you are after is as follows:

1) do your calculation (below is my example for a COGS):

COGS = abs(sum(itemmasterdim[StandardCost]*sum(TransactionHistory[TransactionQuantity]))

2) do a SUMX on your new measure:

Total COGS = SUMX(TransactionHistory , [COGS])

*Note the expression is SUMX(Table, Expression)... this requires you to select the table in which the expression (measure) is located followed by a comma and the expreassion.

This will calculate the total of the column. do not add your original measurement into the visual instead add your new measure into it --- it will work the correct way down the rows and when the totals are measure it will only do the calculation of the totals column - this works for multiple rows.

Regards,

Jose

Frequent Visitor

I wasnt able to solve it with the provided answers. I have the following data set:

Im trying to SUM the WMAPE values. Im expecting the answer to be (2.08+6.45+6.22+1.75.........)

How do i solve this one ?

Thanks!

So far the only solution I've found to using measures, is to do it at the query level. ie. A totally custom written SQL that does all this work before PowerBI has a chance to 'measure' it. Measures work fine until you do anything but display them in the raw. So far, none of the FILTER tricks work as expected. It's really annoying to be honest as the whole point of PowerBI is to relieve the Data-Scientist of time spent designing reports, but to provide reliable data. I'm spending more time in PowerBI than any other tool, and it's highly frustrating.
That said. Once you design the data the specific/custom way you need it calculated. PowerBI does a good job in simplifying the visual design. But the moment you need to do "math" in PowerBI it fails hard.

Super User

Take a look at my tip here:

http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

Seems like you are running into this problem.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors