Conditional format asset returns based upon weight Averages

[ianhan13]

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

[MFelix · Accepted solution]

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

Don't forget to mark the correct answer to help others.

PBIX file attach.

[ianhan13]

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

Ian

[ianhan13]

@MFelix 

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

Many thanks

Ian

[ianhan13]

Hi @MFelix 

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

[MFelix · Accepted solution]

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

Don't forget to mark the correct answer to help others.

PBIX file attach.

[ianhan13]

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

[MFelix · Accepted solution]

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]

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.

[ianhan13]

@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