topic Create a measure of the ratio of value in same column by different groups in different columns in Desktop
https://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/969947#M464358
<P> </P><P>I have below data. </P><P> </P><P>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.</P><P> </P><P>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?</P><P> </P><P><U>Table 1 Cost Split of Item Sold</U></P><TABLE><TBODY><TR><TD>Item</TD><TD>Category</TD><TD>Brand</TD><TD>Cost Split Ratio</TD><TD>Currency</TD><TD>Cost Split (in USD)</TD></TR><TR><TD>Item 1</TD><TD>Cat A</TD><TD>Brand D</TD><TD>0.50</TD><TD>HKD</TD><TD>5.00</TD></TR><TR><TD>Item 1</TD><TD>Cat A</TD><TD>Brand E</TD><TD>0.30</TD><TD>HKD</TD><TD>3.00</TD></TR><TR><TD>Item 1</TD><TD>Cat B</TD><TD>Brand F</TD><TD>0.20</TD><TD>HKD</TD><TD>2.00</TD></TR><TR><TD>Item 2</TD><TD>Cat B</TD><TD>Brand F</TD><TD>0.40</TD><TD>JPY</TD><TD>4.80</TD></TR><TR><TD>Item 2</TD><TD>Cat C</TD><TD>Brand D</TD><TD>0.60</TD><TD>JPY</TD><TD>7.20</TD></TR><TR><TD>Item 3</TD><TD>Cat A</TD><TD>Brand E</TD><TD>0.20</TD><TD>USD</TD><TD>3.00</TD></TR><TR><TD>Item 3</TD><TD>Cat C</TD><TD>Brand F</TD><TD>0.10</TD><TD>USD</TD><TD>1.50</TD></TR><TR><TD>Item 3</TD><TD>Cat B</TD><TD>Brand D</TD><TD>0.70</TD><TD>USD</TD><TD>10.50</TD></TR><TR><TD>Item 4</TD><TD>Cat C</TD><TD>Brand E</TD><TD>0.20</TD><TD>CNY</TD><TD>1.60</TD></TR><TR><TD>Item 4</TD><TD>Cat B</TD><TD>Brand E</TD><TD>0.80</TD><TD>CNY</TD><TD>6.40</TD></TR><TR><TD>Item 5</TD><TD>Cat C</TD><TD>Brand F</TD><TD>1.00</TD><TD>KRW</TD><TD>11.00</TD></TR></TBODY></TABLE><P> </P><P><U>Table 2 Total Cost of Items Sold</U></P><TABLE><TBODY><TR><TD>Item</TD><TD>Currency</TD><TD>Cost</TD></TR><TR><TD>Item 1</TD><TD>HKD</TD><TD>77.7</TD></TR><TR><TD>Item 2</TD><TD>JPY</TD><TD>1262.52</TD></TR><TR><TD>Item 3</TD><TD>USD</TD><TD>15</TD></TR><TR><TD>Item 4</TD><TD>CNY</TD><TD>55.6</TD></TR><TR><TD>Item 5</TD><TD>KRW</TD><TD>13101.77</TD></TR></TBODY></TABLE><P> </P><P><U>Table 3 Exchange Rate</U></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Currency</TD><TD>Exchange Rate</TD></TR><TR><TD>HKD</TD><TD>7.77</TD></TR><TR><TD>USD</TD><TD>1</TD></TR><TR><TD>CNY</TD><TD>6.95</TD></TR><TR><TD>KRW</TD><TD>1191.07</TD></TR><TR><TD>JPY</TD><TD>105.21</TD></TR></TBODY></TABLE><P> </P><P><U>Table 4</U></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD> </TD><TD>Cat A</TD><TD>Cat B</TD><TD>Cat C</TD></TR><TR><TD>Brand D</TD><TD>45.5%</TD><TD>44.3%</TD><TD>33.8%</TD></TR><TR><TD>Brand E</TD><TD>54.5%</TD><TD>27.0%</TD><TD>7.5%</TD></TR><TR><TD>Brand F</TD><TD>0.0%</TD><TD>28.7%</TD><TD>58.7%</TD></TR></TBODY></TABLE><P class="ics-element-donot-delete 1001_CC@"> </P>Thu, 12 Mar 2020 01:44:05 GMTtrinachung2020-03-12T01:44:05ZCreate a measure of the ratio of value in same column by different groups in different columns
https://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/969947#M464358
<P> </P><P>I have below data. </P><P> </P><P>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.</P><P> </P><P>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?</P><P> </P><P><U>Table 1 Cost Split of Item Sold</U></P><TABLE><TBODY><TR><TD>Item</TD><TD>Category</TD><TD>Brand</TD><TD>Cost Split Ratio</TD><TD>Currency</TD><TD>Cost Split (in USD)</TD></TR><TR><TD>Item 1</TD><TD>Cat A</TD><TD>Brand D</TD><TD>0.50</TD><TD>HKD</TD><TD>5.00</TD></TR><TR><TD>Item 1</TD><TD>Cat A</TD><TD>Brand E</TD><TD>0.30</TD><TD>HKD</TD><TD>3.00</TD></TR><TR><TD>Item 1</TD><TD>Cat B</TD><TD>Brand F</TD><TD>0.20</TD><TD>HKD</TD><TD>2.00</TD></TR><TR><TD>Item 2</TD><TD>Cat B</TD><TD>Brand F</TD><TD>0.40</TD><TD>JPY</TD><TD>4.80</TD></TR><TR><TD>Item 2</TD><TD>Cat C</TD><TD>Brand D</TD><TD>0.60</TD><TD>JPY</TD><TD>7.20</TD></TR><TR><TD>Item 3</TD><TD>Cat A</TD><TD>Brand E</TD><TD>0.20</TD><TD>USD</TD><TD>3.00</TD></TR><TR><TD>Item 3</TD><TD>Cat C</TD><TD>Brand F</TD><TD>0.10</TD><TD>USD</TD><TD>1.50</TD></TR><TR><TD>Item 3</TD><TD>Cat B</TD><TD>Brand D</TD><TD>0.70</TD><TD>USD</TD><TD>10.50</TD></TR><TR><TD>Item 4</TD><TD>Cat C</TD><TD>Brand E</TD><TD>0.20</TD><TD>CNY</TD><TD>1.60</TD></TR><TR><TD>Item 4</TD><TD>Cat B</TD><TD>Brand E</TD><TD>0.80</TD><TD>CNY</TD><TD>6.40</TD></TR><TR><TD>Item 5</TD><TD>Cat C</TD><TD>Brand F</TD><TD>1.00</TD><TD>KRW</TD><TD>11.00</TD></TR></TBODY></TABLE><P> </P><P><U>Table 2 Total Cost of Items Sold</U></P><TABLE><TBODY><TR><TD>Item</TD><TD>Currency</TD><TD>Cost</TD></TR><TR><TD>Item 1</TD><TD>HKD</TD><TD>77.7</TD></TR><TR><TD>Item 2</TD><TD>JPY</TD><TD>1262.52</TD></TR><TR><TD>Item 3</TD><TD>USD</TD><TD>15</TD></TR><TR><TD>Item 4</TD><TD>CNY</TD><TD>55.6</TD></TR><TR><TD>Item 5</TD><TD>KRW</TD><TD>13101.77</TD></TR></TBODY></TABLE><P> </P><P><U>Table 3 Exchange Rate</U></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Currency</TD><TD>Exchange Rate</TD></TR><TR><TD>HKD</TD><TD>7.77</TD></TR><TR><TD>USD</TD><TD>1</TD></TR><TR><TD>CNY</TD><TD>6.95</TD></TR><TR><TD>KRW</TD><TD>1191.07</TD></TR><TR><TD>JPY</TD><TD>105.21</TD></TR></TBODY></TABLE><P> </P><P><U>Table 4</U></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD> </TD><TD>Cat A</TD><TD>Cat B</TD><TD>Cat C</TD></TR><TR><TD>Brand D</TD><TD>45.5%</TD><TD>44.3%</TD><TD>33.8%</TD></TR><TR><TD>Brand E</TD><TD>54.5%</TD><TD>27.0%</TD><TD>7.5%</TD></TR><TR><TD>Brand F</TD><TD>0.0%</TD><TD>28.7%</TD><TD>58.7%</TD></TR></TBODY></TABLE><P class="ics-element-donot-delete 1001_CC@"> </P>Thu, 12 Mar 2020 01:44:05 GMThttps://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/969947#M464358trinachung2020-03-12T01:44:05ZRe: Create a measure of the ratio of value in same column by different groups in different columns
https://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/970018#M464393
<P>New columns in table 1</P>
<LI-CODE lang="markup">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]</LI-CODE>
<P> </P>
<P><STRONG>Appreciate your Kudos.</STRONG></P>Thu, 12 Mar 2020 02:30:06 GMThttps://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/970018#M464393amitchandak2020-03-12T02:30:06ZRe: Create a measure of the ratio of value in same column by different groups in different columns
https://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/970181#M464470
<P>Hi <LI-USER uid="214705"></LI-USER> </P>
<P>Create a measure</P>
<LI-CODE lang="markup">Measure =
SUM ( Table1[cost split usd] )
/ CALCULATE (
SUM ( Table1[cost split usd] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Category]
= MAX ( Table1[Category] )
)
)
</LI-CODE>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture8.JPG" style="width: 910px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/245383i17B693B195F201A8/image-size/large?v=1.0&px=999" title="Capture8.JPG" alt="Capture8.JPG" /></span></P>
<P><FONT style="background-color: #ffffff;">Best Regards<BR />Maggie<BR />Community Support Team _ Maggie Li<BR />If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.</FONT></P>Thu, 12 Mar 2020 05:22:51 GMThttps://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/970181#M464470v-juanli-msft2020-03-12T05:22:51ZRe: Create a measure of the ratio of value in same column by different groups in different columns
https://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/971864#M465239
<P>Thanks for the solution, Maggie!</P>Fri, 13 Mar 2020 07:29:21 GMThttps://community.powerbi.com/t5/Desktop/Create-a-measure-of-the-ratio-of-value-in-same-column-by/m-p/971864#M465239trinachung2020-03-13T07:29:21Z