topic Conditional format asset returns based upon weight Averages in Desktop
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1622820#M654000
<P>Hi</P><P>Really would appreciate someones help on this.</P><P>I have a table containing the value and returns (e.g. 1 day%, 1 week %,1 month%) for a range of assets grouped into different categories. A simplified dat table would look like this:</P><P> </P><TABLE><TBODY><TR><TD>Asset Type</TD><TD>Asset Name</TD><TD>Value £</TD><TD>1 day %</TD><TD>1 week %</TD><TD>1 Month %</TD></TR><TR><TD>Stock</TD><TD>Stock 1</TD><TD>100</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Stock</TD><TD>Stock2</TD><TD>50</TD><TD>2</TD><TD>-2</TD><TD>-1</TD></TR><TR><TD>Stock</TD><TD>Stock 3</TD><TD>90</TD><TD>4</TD><TD>6</TD><TD>9</TD></TR><TR><TD>Bond</TD><TD>Bond 1</TD><TD>45</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Bond</TD><TD>Bond 2</TD><TD>45</TD><TD>1</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Bond</TD><TD>Bond 3</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>-3</TD></TR><TR><TD>Other </TD><TD>Other 1</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Other </TD><TD>Other 2 </TD><TD>70</TD><TD>-1</TD><TD>-2</TD><TD>-4</TD></TR><TR><TD>Other </TD><TD>Other 3</TD><TD>80</TD><TD>0</TD><TD>2</TD><TD>3</TD></TR></TBODY></TABLE><P> </P><P>I would like to create a matrix which computes weight average return by Asset Type and an overal weight average return for each period. In excel it looks like this:</P><P> </P><TABLE><TBODY><TR><TD> </TD><TD>Value £</TD><TD>1 day %</TD><TD>1 week %</TD><TD>1 Month %</TD></TR><TR><TD>Stocks</TD><TD>240</TD><TD>2.33</TD><TD>2.67</TD><TD>5.25</TD></TR><TR><TD>Stock 1</TD><TD>100</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Stock2</TD><TD>50</TD><TD>2</TD><TD>-2</TD><TD>-1</TD></TR><TR><TD>Stock 3</TD><TD>90</TD><TD>4</TD><TD>6</TD><TD>9</TD></TR><TR><TD>Bonds</TD><TD>150</TD><TD>0.70</TD><TD>1.40</TD><TD>-0.30</TD></TR><TR><TD>Bond 1</TD><TD>45</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Bond 2</TD><TD>45</TD><TD>1</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Bond 3</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>-3</TD></TR><TR><TD>Other</TD><TD>210</TD><TD>-0.05</TD><TD>0.67</TD><TD>1.24</TD></TR><TR><TD>Other 1</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Other 2 </TD><TD>70</TD><TD>-1</TD><TD>-2</TD><TD>-4</TD></TR><TR><TD>Other 3</TD><TD>80</TD><TD>0</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Weighted Av.</TD><TD> </TD><TD>1.09</TD><TD>2.40</TD><TD>3.04</TD></TR></TBODY></TABLE><P> </P><P>I would then like to conditionally colour each cell based on: </P><P>If cell value is greater than 1.2 x weighted average for period - Green</P><P>If cell value is less than 0.8 x weighted average for period - Red</P><P> </P><P>I am hoping I can do the calculations in the matrix without having to create lots of measures per return period as there are 8 periods X 11 asset types x 40 assets.</P><P><BR />All inputs gratefully received</P><P>Kind regards</P><P> </P><P>Ian</P><P> </P>Tue, 26 Jan 2021 06:57:35 GMTianhan132021-01-26T06:57:35ZConditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1622820#M654000
<P>Hi</P><P>Really would appreciate someones help on this.</P><P>I have a table containing the value and returns (e.g. 1 day%, 1 week %,1 month%) for a range of assets grouped into different categories. A simplified dat table would look like this:</P><P> </P><TABLE><TBODY><TR><TD>Asset Type</TD><TD>Asset Name</TD><TD>Value £</TD><TD>1 day %</TD><TD>1 week %</TD><TD>1 Month %</TD></TR><TR><TD>Stock</TD><TD>Stock 1</TD><TD>100</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Stock</TD><TD>Stock2</TD><TD>50</TD><TD>2</TD><TD>-2</TD><TD>-1</TD></TR><TR><TD>Stock</TD><TD>Stock 3</TD><TD>90</TD><TD>4</TD><TD>6</TD><TD>9</TD></TR><TR><TD>Bond</TD><TD>Bond 1</TD><TD>45</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Bond</TD><TD>Bond 2</TD><TD>45</TD><TD>1</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Bond</TD><TD>Bond 3</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>-3</TD></TR><TR><TD>Other </TD><TD>Other 1</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Other </TD><TD>Other 2 </TD><TD>70</TD><TD>-1</TD><TD>-2</TD><TD>-4</TD></TR><TR><TD>Other </TD><TD>Other 3</TD><TD>80</TD><TD>0</TD><TD>2</TD><TD>3</TD></TR></TBODY></TABLE><P> </P><P>I would like to create a matrix which computes weight average return by Asset Type and an overal weight average return for each period. In excel it looks like this:</P><P> </P><TABLE><TBODY><TR><TD> </TD><TD>Value £</TD><TD>1 day %</TD><TD>1 week %</TD><TD>1 Month %</TD></TR><TR><TD>Stocks</TD><TD>240</TD><TD>2.33</TD><TD>2.67</TD><TD>5.25</TD></TR><TR><TD>Stock 1</TD><TD>100</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Stock2</TD><TD>50</TD><TD>2</TD><TD>-2</TD><TD>-1</TD></TR><TR><TD>Stock 3</TD><TD>90</TD><TD>4</TD><TD>6</TD><TD>9</TD></TR><TR><TD>Bonds</TD><TD>150</TD><TD>0.70</TD><TD>1.40</TD><TD>-0.30</TD></TR><TR><TD>Bond 1</TD><TD>45</TD><TD>0</TD><TD>0</TD><TD>0</TD></TR><TR><TD>Bond 2</TD><TD>45</TD><TD>1</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Bond 3</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>-3</TD></TR><TR><TD>Other</TD><TD>210</TD><TD>-0.05</TD><TD>0.67</TD><TD>1.24</TD></TR><TR><TD>Other 1</TD><TD>60</TD><TD>1</TD><TD>2</TD><TD>5</TD></TR><TR><TD>Other 2 </TD><TD>70</TD><TD>-1</TD><TD>-2</TD><TD>-4</TD></TR><TR><TD>Other 3</TD><TD>80</TD><TD>0</TD><TD>2</TD><TD>3</TD></TR><TR><TD>Weighted Av.</TD><TD> </TD><TD>1.09</TD><TD>2.40</TD><TD>3.04</TD></TR></TBODY></TABLE><P> </P><P>I would then like to conditionally colour each cell based on: </P><P>If cell value is greater than 1.2 x weighted average for period - Green</P><P>If cell value is less than 0.8 x weighted average for period - Red</P><P> </P><P>I am hoping I can do the calculations in the matrix without having to create lots of measures per return period as there are 8 periods X 11 asset types x 40 assets.</P><P><BR />All inputs gratefully received</P><P>Kind regards</P><P> </P><P>Ian</P><P> </P>Tue, 26 Jan 2021 06:57:35 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1622820#M654000ianhan132021-01-26T06:57:35ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626373#M654924
<P>Hi <LI-USER uid="278409"></LI-USER> ,</P>
<P> </P>
<P>You can do the following:</P>
<UL>
<LI>Unpivot the period columns on your data</LI>
<LI>Create a new table using the following code:</LI>
</UL>
<LI-CODE lang="markup">Period = UNION(DISTINCT('Assets'[Attribute]); {"Value £"})</LI-CODE>
<UL>
<LI>Create the following measure:</LI>
</UL>
<LI-CODE lang="markup">Values =
IF (
SELECTEDVALUE ( 'Period'[Attribute] ) = "Value £";
SUMX ( VALUES ( 'Assets'[Asset Name] ); AVERAGE ( 'Assets'[Value £] ) );
CALCULATE (
SUMX ( 'Assets'; 'Assets'[Value] * 'Assets'[Value £] )
/ SUM ( 'Assets'[Value £] );
FILTER (
ALLSELECTED ( 'Assets'[Attribute] );
'Assets'[Attribute] = SELECTEDVALUE ( 'Period'[Attribute] )
)
)
)</LI-CODE>
<P> </P>
<UL>
<LI>Configure your matrix in the following way:
<UL>
<LI>Rows:
<UL>
<LI>Asset Type</LI>
<LI>Asset Name</LI>
</UL>
</LI>
<LI>Columns
<UL>
<LI>Periods from the Period table</LI>
</UL>
</LI>
<LI>Values
<UL>
<LI>Values measure</LI>
</UL>
</LI>
</UL>
</LI>
</UL>
<P>Result below and in attach PBIX file:</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="MFelix_0-1611758645914.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/441526i6CB6921B17C1C09F/image-size/medium?v=v2&px=400" role="button" title="MFelix_0-1611758645914.png" alt="MFelix_0-1611758645914.png" /></span></P>
<P>I have only one question regarding the last line of the Weight average on the last line how are you calculating the values since all the calculations I have tried don't give the same value.</P>Wed, 27 Jan 2021 14:45:23 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626373#M654924MFelix2021-01-27T14:45:23ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626830#M655061
<P><LI-USER uid="223" login="miguel"></LI-USER></P><P>Hi Miguel</P><P>Many thanks for a super quick and comprehensive response I will download and try out the attached file.</P><P>The last lien of weighted averages is just a weighted average of the subtotals to give an overal average. I t should come to the same answer as taking all the individual line items.</P><P>ie</P><P>(Sub total Shares * Total value Shares + sub Total Bonds * Total Value Bond + sub Total Other * Total Value Other)/ Total Value</P><P> </P>Wed, 27 Jan 2021 17:45:22 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626830#M655061ianhan132021-01-27T17:45:22ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626836#M655062
<P><LI-USER uid="14361"></LI-USER> </P><P>Which measure do I then use to do the conditional format of each cell based on Column Average +/- 20%?</P><P>Many thanks</P><P>Ian</P>Wed, 27 Jan 2021 17:47:52 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626836#M655062ianhan132021-01-27T17:47:52ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626840#M655063
<P>Hi <LI-USER uid="14361"></LI-USER> </P><P>Having checked my final row columns seems I have a maths error in week and month returns your values are perfectly correct <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P>Wed, 27 Jan 2021 17:50:35 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626840#M655063ianhan132021-01-27T17:50:35ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626918#M655082
<P>Hi <LI-USER uid="278409"></LI-USER> </P>
<P> </P>
<P>If the calculations is correct then for the condittional formatting use the following measure:</P>
<P> </P>
<LI-CODE lang="markup">Conditional_Formatting =
VAR temp_table =
SUMMARIZE (
ALL ( Assets[Asset Name], Assets[Asset Type], Assets[Attribute], Assets[Value] ),
Assets[Attribute],
"Total Value", [Values]
)
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( Period[Period] ) = "Value £", BLANK (),
[Values]
> SUMX ( temp_table, [Total Value] ) * 1.2, "Green",
[Values]
< SUMX ( temp_table, [Total Value] ) * 0.8, "Red"
)</LI-CODE>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="MFelix_0-1611771803345.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/441674i5A6AFF9D4FEF5B89/image-size/medium?v=v2&px=400" role="button" title="MFelix_0-1611771803345.png" alt="MFelix_0-1611771803345.png" /></span></P>
<P> </P>
<P>Don't forget to mark the correct answer to help others.</P>
<P>PBIX file attach.</P>Wed, 27 Jan 2021 18:23:50 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1626918#M655082MFelix2021-01-27T18:23:50ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1629253#M655722
<P>Hi <LI-USER uid="14361"></LI-USER> </P><P>Many thanks for your solution which works perfectly !!</P><P>One last question on this I promise <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P>Is there a way to apply the +/- conditional formatting based upon the weight average of the Subtotal in the column rather than the whole column </P><P>i.e. stocks measured +/- 20% against weighted average of Stock for the return period not the total weighted average for the return period.</P><P>Hopefully I have explained that clearly enough?</P><P>Kind regards</P><P>Ian</P>Thu, 28 Jan 2021 14:27:33 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1629253#M655722ianhan132021-01-28T14:27:33ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1629440#M655773
<P>Hi <LI-USER uid="278409"></LI-USER> ,</P>
<P> </P>
<P>Redo the measure to:</P>
<LI-CODE lang="markup">Conditional_Formatting =
VAR temp_table =
SUMMARIZE (
ALLSELECTED (
Assets[Asset Type],
Assets[Attribute],
Assets[Asset Name],
Assets[Value]
),
Assets[Attribute],
Assets[Asset Type],
"Total Value", [Values]
)
VAR AssetTypeTotal =
SUMX (
FILTER ( temp_table, Assets[Asset Type] = SELECTEDVALUE ( Assets[Asset Type] ) ),
[Total Value]
)
RETURN
IF (
ISFILTERED ( Assets[Asset Name] ),
SWITCH (
TRUE (),
SELECTEDVALUE ( Period[Period] ) = "Value £", BLANK (),
[Values] > AssetTypeTotal * 1.2, "Green",
[Values] < AssetTypeTotal * 0.8, "Red"
)
)</LI-CODE>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="MFelix_0-1611847616000.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/442517i417E83E9A0B97091/image-size/medium?v=v2&px=400" role="button" title="MFelix_0-1611847616000.png" alt="MFelix_0-1611847616000.png" /></span></P>
<P> </P>
<P> </P>
<P> </P>Thu, 28 Jan 2021 15:27:14 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1629440#M655773MFelix2021-01-28T15:27:14ZRe: Conditional format asset returns based upon weight Averages
https://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1630117#M655977
<P><LI-USER uid="14361"></LI-USER> that is fantastic thank you so very much for such a great response</P><P>Ian</P>Thu, 28 Jan 2021 21:48:23 GMThttps://community.powerbi.com/t5/Desktop/Conditional-format-asset-returns-based-upon-weight-Averages/m-p/1630117#M655977ianhan132021-01-28T21:48:23Z