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.
Hi Folks,
I am trying to convert some currencies then multiply by a weighting factor for each row then summarise in a matrix table by Months.
The weight value table looks like this:
Probabiltiy | Value |
15 | 10 |
20 | 20 |
40 | 40 |
60 | 60 |
80 | 80 |
90 | 80 |
The Currency conversion only has one value at this point:
Financial Year | Conversion Rate |
2019 | 1.17 |
Then main table has data similiar to this
Proability | Total amount | Currency |
15 | 0 | NZ$ |
15 | 172 | NZ$ |
15 | 346 | NZ$ |
15 | 845 | NZ$ |
15 | 3388 | NZ$ |
15 | 241 | AUS$ |
20 | 328 | AUS$ |
20 | 984 | AUS$ |
20 | 1102 | AUS$ |
40 | -9604 | AUS$ |
40 | -3051 | AUS$ |
40 | -1419 | AUS$ |
40 | 0 | AUS$ |
40 | 76 | AUS$ |
40 | 99 | AUS$ |
40 | 108 | AUS$ |
40 | 37704 | AUS$ |
The relationships are quite standard
The Calculation should work out if the currency is NOT AUS$ then divide by the conversion rate and divide by 1000 so it is at the (K) level then multiply by the Value for each row in the table then sum it up for the month in a matrix table - I thought Sumx did this.What I have currently
Weighted Value AUD(K) = CALCULATE(CALCULATE(SUMX(Opportunity, IF(Opportunity[Currency]<>"AUS$", Opportunity[Total amount]/RELATED('Currency'[Conversion rate]),Opportunity[Total amount])/1000))*SUMX('Probability','Probability'[Value])/100)
I believe it is doing the row by row calculation but not 100% sure as the result I see when I do this in excel is not what I see in my report.
Thanks in Advance
Binway
Solved! Go to Solution.
Hi @Binway
Here is how I would suggest writing your measure:
Weighted Value AUD(K) = SUMX ( SUMMARIZE ( Opportunity, Opportunity[Currency], 'Probability'[Value], Currency[Conversion Rate] ), VAR TotalAmount = CALCULATE ( SUM ( Opportunity[Total amount] ) ) VAR ConversionRateFinal = IF ( Opportunity[Currency] = "AUS$", 1, Currency[Conversion Rate] ) VAR ProbabilityFinal = 'Probability'[Value] / 100 RETURN TotalAmount * ProbabilityFinal / ConversionRateFinal / 1000 )
SUMMARIZE(...) gives you the distinct combinations of Currency, Probability Value & Conversion Rate existing in Opportunity in the current filter context, noting that SUMMARIZE will "follow" relationships from the many-side to the one-side.
SUMX iterates through those combinations and calculates the required values using variables to help with readability, combining them in the expression following RETURN (I think I got this right anyway 🙂 ).
By the way, you could also have also fixed your original expression by referring to RELATED ( 'Probability'[Value] ) within the SUMX.
Does this give you the expected result? Please post back if needed.
Regards,
Owen
Hi @Binway
Here is how I would suggest writing your measure:
Weighted Value AUD(K) = SUMX ( SUMMARIZE ( Opportunity, Opportunity[Currency], 'Probability'[Value], Currency[Conversion Rate] ), VAR TotalAmount = CALCULATE ( SUM ( Opportunity[Total amount] ) ) VAR ConversionRateFinal = IF ( Opportunity[Currency] = "AUS$", 1, Currency[Conversion Rate] ) VAR ProbabilityFinal = 'Probability'[Value] / 100 RETURN TotalAmount * ProbabilityFinal / ConversionRateFinal / 1000 )
SUMMARIZE(...) gives you the distinct combinations of Currency, Probability Value & Conversion Rate existing in Opportunity in the current filter context, noting that SUMMARIZE will "follow" relationships from the many-side to the one-side.
SUMX iterates through those combinations and calculates the required values using variables to help with readability, combining them in the expression following RETURN (I think I got this right anyway 🙂 ).
By the way, you could also have also fixed your original expression by referring to RELATED ( 'Probability'[Value] ) within the SUMX.
Does this give you the expected result? Please post back if needed.
Regards,
Owen
and thanks for the explanation of summarize etc.
Regards
Binway
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |