cancel
Showing results for
Search instead for
Did you mean:  Helper III

## 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

2 ACCEPTED SOLUTIONS  Super User III

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.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português   Super User III

Hi @ianhan13 ,

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"
)
)`````` Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português 8 REPLIES 8  Helper III

@MFelix  that is fantastic thank you so very much for such a great response

Ian  Helper III

Which measure do I then use to do the conditional format of each cell based on Column Average +/- 20%?

Many thanks

Ian  Helper III

Hi @MFelix

Having checked my final row columns seems I have a maths error in week and month returns your values are perfectly correct 🙂  Super User III

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.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português   Helper III

Hi @MFelix

Many thanks for your solution which works perfectly !!

One last question on this I promise 🙂

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

i.e. stocks measured +/- 20% against weighted average of Stock for the return period not the total weighted average for the return period.

Hopefully I have explained that clearly enough?

Kind regards

Ian  Super User III

Hi @ianhan13 ,

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"
)
)`````` Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português   Super User III

Hi  @ianhan13 ,

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

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.

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português   Helper III

@miguel

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  ## Helpful resources

Announcements #### Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview. #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (6,260)