topic Re: Sum of values on separate rows for a complicated formula. in DAX Commands and Tips

Sum of values on separate rows for a complicated formula.

ikibirev — Mon, 20 Dec 2021 09:11:52 GMT

Hello!

I have a table like that (I've simplified a real case to make it easier to demonstrate.):

There are two columns with some numeric values (Value 1 and Value2) and two columns with weights (Weight1 and Weight2), which are calculated according to the following formulas:

Weight1 = divide('Table1'[Value1], calculate('Table1'[Value1], allselected('Table1')))

Weight2 = divide('Table1'[Value2], calculate('Table1'[Value2], allselected('Table1')))

Then I try to calculate the effect using some complicated formula. For example, like this:

Effect = (Weigth2 - Weight1) * Value2 / Value1

So I got perfect row result. But on total line I got 0,00. This is correct if we apply the formula, but I want to show the sum by rows.

Maybe in different measure... I need to calculate 0,11 - 0,10 + 0,11 = 0,12

How to calculate it?

Re: Sum of values on separate rows for a complicated formula.

amitchandak — Mon, 20 Dec 2021 09:32:11 GMT

ikibirev , hope these are measures like

Weight1 = divide(sum('Table1'[Value1]), calculate(sum('Table1'[Value1]), allselected('Table1')))

Weight2 = divide(sum('Table1'[Value2], calculate(sum('Table1'[Value2]), allselected('Table1')))

Effect = divide((Weigth2 - Weight1) * sum(Table1[Value2]) , sum(Table1[Value1]))

Re: Sum of values on separate rows for a complicated formula.

ikibirev — Mon, 20 Dec 2021 10:01:41 GMT

Amitchandak, thank you very much for your answer!

But I got zero value in total line for this variant of formula too...

How do I get the sum of the rows?

0,11 + (-0,10) + 0,11 = 0,12

Re: Sum of values on separate rows for a complicated formula.

Anonymous — Wed, 05 Jan 2022 07:46:09 GMT

Hi ikibirev ,

Please try to update the formula of your measure [Effect] as below and check whether it can get the correct result. Please find the details in the attachment.

PS: The part with red font is new added one...

Effect =

VAR _tab =

ADDCOLUMNS (

ALLEXCEPT ( 'Table1', 'Table1'[Type] ),

"@effect",

( [Weight2] - [Weight1] ) * [Value2] / [Value1]

)

RETURN

SUMX ( _tab, [@effect] )

If the above one can't help you get the correct result, please provide some raw date(exclude sensitive data) and your expected result with calculation logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards

Re: Sum of values on separate rows for a complicated formula.

ikibirev — Mon, 10 Jan 2022 09:32:32 GMT

You are a genius! You cannot imagine how grateful I'm to you! Many thanks!
It works! It remains to realize exactly how... 😉