Sum of values on separate rows for a complicated formula.

[ikibirev]

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?

[amitchandak]

@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]))

[ikibirev]

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

[v-yiruan-msft · Accepted solution]

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

[ikibirev]

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