Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 simplif...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

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

     

     

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

    PBIX file attach.

  • MFelix's avatar
    MFelix
    5 years ago

    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's avatar
MFelix
Super User
5 years ago

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

 

 

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

PBIX file attach.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    MFelix
    Super User
    5 years ago

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