cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Create a measure of the ratio of value in same column by different groups in different columns

I have below data.

Items 1-5 were sold and the cost of them are splitted with below ratio by categories and brands.  The last 2 columns are the working steps and the values are look up from Table 2 & 3 with calculated formula set.  Cost Split (in USD) is the total cost * cost split ratio * exchange rate.

I wanna create a measure in Power BI which calculates the ratio of cost split by brands and cost split by categories, i.e. sum of total cost split by brands / sum of total cost split by categories, and the results will be shown in Pivot Table format (Table 4).  For example,  the ratio of Brand E in Cat A is 6 / 11 = 54.5%.  How to set the measure to get the result in Power BI Pivot Table?

Table 1 Cost Split of Item Sold

 Item Category Brand Cost Split Ratio Currency Cost Split (in USD) Item 1 Cat A Brand D 0.50 HKD 5.00 Item 1 Cat A Brand E 0.30 HKD 3.00 Item 1 Cat B Brand F 0.20 HKD 2.00 Item 2 Cat B Brand F 0.40 JPY 4.80 Item 2 Cat C Brand D 0.60 JPY 7.20 Item 3 Cat A Brand E 0.20 USD 3.00 Item 3 Cat C Brand F 0.10 USD 1.50 Item 3 Cat B Brand D 0.70 USD 10.50 Item 4 Cat C Brand E 0.20 CNY 1.60 Item 4 Cat B Brand E 0.80 CNY 6.40 Item 5 Cat C Brand F 1.00 KRW 11.00

Table 2 Total Cost of Items Sold

 Item Currency Cost Item 1 HKD 77.7 Item 2 JPY 1262.52 Item 3 USD 15 Item 4 CNY 55.6 Item 5 KRW 13101.77

Table 3 Exchange Rate

 Currency Exchange Rate HKD 7.77 USD 1 CNY 6.95 KRW 1191.07 JPY 105.21

Table 4

 Cat A Cat B Cat C Brand D 45.5% 44.3% 33.8% Brand E 54.5% 27.0% 7.5% Brand F 0.0% 28.7% 58.7%

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Create a measure of the ratio of value in same column by different groups in different columns

Create a measure

``````Measure =
SUM ( Table1[cost split usd] )
/ CALCULATE (
SUM ( Table1[cost split usd] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Category]
= MAX ( Table1[Category] )
)
)
``````

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Highlighted
Super User IV

## Re: Create a measure of the ratio of value in same column by different groups in different columns

New columns in table 1

``````Total cost = maxx(filter(table2,table1[item]=table2[item] && table2[Currency] = table1[Currency]),table2[Cost])
exchange rate =maxx(filter(table3,table3[Currency] = table1[Currency]),table3[Exchange Rate])
Cost Split (in USD) = [Total cost] * [cost split ratio] * [exchange rate]``````

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support

## Re: Create a measure of the ratio of value in same column by different groups in different columns

Create a measure

``````Measure =
SUM ( Table1[cost split usd] )
/ CALCULATE (
SUM ( Table1[cost split usd] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Category]
= MAX ( Table1[Category] )
)
)
``````

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper I

## Re: Create a measure of the ratio of value in same column by different groups in different columns

Thanks for the solution, Maggie!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors