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

[trinachung]

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%

[v-juanli-msft · Accepted solution]

Hi @trinachung 

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.

[trinachung]

Thanks for the solution, Maggie!

[amitchandak]

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]

Appreciate your Kudos.