Total Sum of a measure

[cmplieger]

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?

[Anonymous]

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

[dkomsthoeft]

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!

[jcrodriguez]

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

[ricardobrubaker]

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!

[Michael-Lowden]

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.

[Greg_Deckler · Accepted solution]

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.