Forum Discussion
Conditional format asset returns based upon weight Averages
Hi
Really would appreciate someones help on this.
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:
| Asset Type | Asset Name | Value £ | 1 day % | 1 week % | 1 Month % |
| Stock | Stock 1 | 100 | 1 | 2 | 5 |
| Stock | Stock2 | 50 | 2 | -2 | -1 |
| Stock | Stock 3 | 90 | 4 | 6 | 9 |
| Bond | Bond 1 | 45 | 0 | 0 | 0 |
| Bond | Bond 2 | 45 | 1 | 2 | 3 |
| Bond | Bond 3 | 60 | 1 | 2 | -3 |
| Other | Other 1 | 60 | 1 | 2 | 5 |
| Other | Other 2 | 70 | -1 | -2 | -4 |
| Other | Other 3 | 80 | 0 | 2 | 3 |
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:
| Value £ | 1 day % | 1 week % | 1 Month % | |
| Stocks | 240 | 2.33 | 2.67 | 5.25 |
| Stock 1 | 100 | 1 | 2 | 5 |
| Stock2 | 50 | 2 | -2 | -1 |
| Stock 3 | 90 | 4 | 6 | 9 |
| Bonds | 150 | 0.70 | 1.40 | -0.30 |
| Bond 1 | 45 | 0 | 0 | 0 |
| Bond 2 | 45 | 1 | 2 | 3 |
| Bond 3 | 60 | 1 | 2 | -3 |
| Other | 210 | -0.05 | 0.67 | 1.24 |
| Other 1 | 60 | 1 | 2 | 5 |
| Other 2 | 70 | -1 | -2 | -4 |
| Other 3 | 80 | 0 | 2 | 3 |
| Weighted Av. | 1.09 | 2.40 | 3.04 |
I would then like to conditionally colour each cell based on:
If cell value is greater than 1.2 x weighted average for period - Green
If cell value is less than 0.8 x weighted average for period - Red
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.
All inputs gratefully received
Kind regards
Ian
Hi Anonymous
If the calculations is correct then for the condittional formatting use the following measure:
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" )Don't forget to mark the correct answer to help others.
PBIX file attach.
Hi Anonymous ,
Redo the measure to:
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" ) )
8 Replies
Hi Anonymous ,
You can do the following:
- Unpivot the period columns on your data
- Create a new table using the following code:
Period = UNION(DISTINCT('Assets'[Attribute]); {"Value £"})- Create the following measure:
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] ) ) ) )- Configure your matrix in the following way:
- Rows:
- Asset Type
- Asset Name
- Columns
- Periods from the Period table
- Values
- Values measure
- Rows:
Result below and in attach PBIX file:
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.
Hi Miguel
Many thanks for a super quick and comprehensive response I will download and try out the attached file.
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.
ie
(Sub total Shares * Total value Shares + sub Total Bonds * Total Value Bond + sub Total Other * Total Value Other)/ Total Value
Which measure do I then use to do the conditional format of each cell based on Column Average +/- 20%?
Many thanks
Ian
Hi MFelix
Having checked my final row columns seems I have a maths error in week and month returns your values are perfectly correct 🙂
Hi Anonymous
If the calculations is correct then for the condittional formatting use the following measure:
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" )Don't forget to mark the correct answer to help others.
PBIX file attach.
MFelix that is fantastic thank you so very much for such a great response
Ian