Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

 

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

 

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
Anonymous
Not applicable

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

Ian

Anonymous
Not applicable

@MFelix 

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

Many thanks

Ian

Anonymous
Not applicable

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

 

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



Anonymous
Not applicable

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

 

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



MFelix
Super User
Super User

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

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



Anonymous
Not applicable

@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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.