topic Re: How to calculate totals by rows? in DAX Commands and Tips

How to calculate totals by rows?

ikibirev — Thu, 13 Jan 2022 10:34:09 GMT

It would seem a simple question, but I can’t solve it in Power BI...

You can get pbix exampe via this link (unfortunately direct loading is not available for me in this forum):

https://www.dropbox.com/s/vmt0z7n8ai3phgq/DAX%20Issue%20Example%202.pbix?dl=0

In this example I'm getting zero values for the totals of columns Effect and MixEffect:

But I need get 130 (35 + 50 + 45) and 830 (140 + 150 + 540).

This example is based on the following simple data:

The following measures are used:

Val1Totals = calculate(sum('TestTable'[Val1]), ALLSELECTED('TestTable'))

Effect = 'TestTable'[Val1Totals] - sum('TestTable'[Val1])

MixEffect = sum('TestTable'[Val2]) * 'TestTable'[Effect]

Or the same result in one formula:

MixEffect2 = (calculate(sum('TestTable'[Val1]), ALLSELECTED('TestTable')) - sum('TestTable'[Val1])) * sum('TestTable'[Val2])

How can I get 830 for MixEffect? May be as another measure... Or an intermediate table...

I would be grateful for any advice. This is not a fictional example, there is a real need to calculate the COGS effect.

Thank you!

Re: How to calculate totals by rows?

amitchandak — Thu, 13 Jan 2022 10:40:44 GMT

ikibirev , Try like

MixEffect = sumX('TestTable', 'TestTable'[Val2] * ([Val1Totals] - 'TestTable'[Val1]) )

Re: How to calculate totals by rows?

ValtteriN — Thu, 13 Jan 2022 10:43:29 GMT

Hi,

The total of a matrix uses the same logic as with the measure in the column. To create a custom total which calculates all of the values you can use SUMX and IF.

Here is a very similar solution to your case:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Total-wrong/m-p/2268244#M55107

The basic dax is as follows:

Custom total =

if(SELECTEDVALUE(table[column])<>BLANK(),[your measure],CALCULATE(SUMX(ALL(table),[your measure])))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: How to calculate totals by rows?

ikibirev — Thu, 13 Jan 2022 10:50:05 GMT

amitchandak,

Unfortunately, the proposed formula gives a different result (1050 instead 830):

Re: How to calculate totals by rows?

ikibirev — Thu, 13 Jan 2022 11:05:23 GMT

ValtteriN,

A very interesting idea! But unfortunately the result is different from zero, but has the wrong value (1450 instead 830):

Re: How to calculate totals by rows?

smpa01 — Thu, 13 Jan 2022 14:30:20 GMT

ikibirev you can write these two measures

_effect = SUMX(VALUES(TestTable[Category]),[Effect]) _effect = SUMX(VALUES(TestTable[Category]),[Effect])

Re: How to calculate totals by rows?

ikibirev — Thu, 13 Jan 2022 14:45:35 GMT

smpa01

Greate! You are Superb!

The formula in your answer was slightly not exactly copy-pated, but I got the idea. Merged this answer with ValtteriN answer.

So the result formulas are:

_mixEffectTotal =

if(selectedvalue('TestTable'[Category]) <> blank(),

[MixEffect],

sumx(values(TestTable[Category]),[MixEffect]))

Thank you very much!