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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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



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



MFelix
Super User
Super User

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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