cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Binway Helper I
Helper I

Calculation at Row Level

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:

ProbabiltiyValue
1510
2020
4040
6060
8080
90

80

 

The Currency conversion only has one value at this point:

Financial YearConversion Rate
20191.17

 Then main table has data similiar to this

ProabilityTotal amountCurrency
150NZ$
15172NZ$
15346NZ$
15845NZ$
153388NZ$
15241AUS$
20328AUS$
20984AUS$
201102AUS$
40-9604AUS$
40-3051AUS$
40-1419AUS$
400AUS$
4076AUS$
4099AUS$
40108AUS$
4037704AUS$

The relationships are quite standard

 

ERD.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

Re: Calculation at Row Level

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
OwenAuger Community Champion
Community Champion

Re: Calculation at Row Level

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Binway Helper I
Helper I

Re: Calculation at Row Level

and thanks for the explanation of summarize etc.

 

Regards

 

Binway

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors