Calculation at Row Level

[Binway]

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

[OwenAuger · Accepted 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

[Binway]

and thanks for the explanation of summarize etc.

Regards

Binway