cancel
Showing results for
Did you mean:
Highlighted
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:

 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.

Binway

1 ACCEPTED SOLUTION

Accepted Solutions
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!

2 REPLIES 2
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!

Helper I

## Re: Calculation at Row Level

and thanks for the explanation of summarize etc.

Regards

Binway

Announcements

#### 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!

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

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