Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 , 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]))
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
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
You are a genius! You cannot imagine how grateful I'm to you! Many thanks!
It works! It remains to realize exactly how... 😉
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |