cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianhan13
Helper III
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 TypeAsset NameValue £1 day %1 week %1 Month %
StockStock 1100125
StockStock2502-2-1
StockStock 390469
BondBond 145000
BondBond 245123
BondBond 36012-3
Other Other 160125
Other Other 2 70-1-2-4
Other Other 380023

 

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 %
Stocks2402.332.675.25
Stock 1100125
Stock2502-2-1
Stock 390469
Bonds1500.701.40-0.30
Bond 145000
Bond 245123
Bond 36012-3
Other210-0.050.671.24
Other 160125
Other 2 70-1-2-4
Other 380023
Weighted Av. 1.092.403.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

Hi @ianhan13 

 

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

 

MFelix_0-1611771803345.png

 

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



View solution in original post

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

 

MFelix_0-1611847616000.png

 

 

 


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



View solution in original post

8 REPLIES 8
ianhan13
Helper III
Helper III

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

Ian

ianhan13
Helper III
Helper III

@MFelix 

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 @ianhan13 

 

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

 

MFelix_0-1611771803345.png

 

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



View solution in original post

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

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

 

MFelix_0-1611847616000.png

 

 

 


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



View solution in original post

MFelix
Super User III
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:

MFelix_0-1611758645914.png

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



@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
User Groups Public Preview

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.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.