Forum Discussion

GarethW's avatar
GarethW
Frequent Visitor
6 years ago
Solved

Conditional formatting of Matrix based on Total average

Hello I am new to PowerBI and am in the process of migrating old manual reports to it.   One of these reports shows share of sales, by month (column) by product type (rows).   This has a hea...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi GarethW ,

     

    First of all let me apologize for the late response I must have missed this post on the multiple notifications I have.

     

    What I think I was abble to get it to work I have created 3 measures (this simplifies the tough process):

    % per category/month =
DIVIDE (
    COUNT ( 'Table'[Region] );
    CALCULATE ( COUNT ( 'Table'[Region] ); ALLSELECTED ( 'Table'[Region] ) )
)



% Total/region =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' );
            'Table'[Region];
            "@count"; COUNT ( 'Table'[Region] )
        );
        'Table'[Region] = MAX ( 'Table'[Region] )
    )
VAR total_table =
    SUMMARIZE (
        ALLSELECTED ( 'Table' );
        'Table'[Region];
        "@count"; COUNT ( 'Table'[Region] )
    )
RETURN
    DIVIDE ( SUMX ( temp_table; [@count] ); SUMX ( total_table; [@count] ) )



% variation = [% per category/month]-[% Total/region]

     

    Now use the last measure to create your condittional formatting.

     

    As you can see on the image below the values that are equal to the region total are blank below are red and above are green.

     

    Check also the PBIX file attached.

     

    Once more I'm very sorry for the delay in the answer.

MFelix's avatar
MFelix
Super User
6 years ago

Hi MAAbdullah_47 ,

 

Taking into consideration that you have a measure taht calculates the difference between quarters that should be the measure you use on your condittional formatting.

 

Be aware that you do not need to have that measure in the matrix the context will allow to evaluate it even if not in the matrix.

MAAbdullah_47's avatar
MAAbdullah_47
Helper V
6 years ago

Sorry, MFelix  I didn't Understand your answer could explain with some details?

 

  • MFelix's avatar
    MFelix
    Super User
    6 years ago
    Hi MAAbdullah_47,

    You refered that you have a measure that calculates the diference between quarters correct?

    If this is true then use thst measure for your conditional formatting.
  • MAAbdullah_47's avatar
    MAAbdullah_47
    Helper V
    6 years ago

    Thank you MFelix  I mean the measure that I need to do diverging in color is (Total Quantity), for the difference measure it is easy to do that because it is (+,-) results, the real challenge is on the measure (Total quantity) needs a programmatic solution using (DAX) that's what I'm looking for, did I make the Idea clear? 

  • MAAbdullah_47's avatar
    MAAbdullah_47
    Helper V
    6 years ago

    I need to do the diverging of the color (Horisintally) for the measure (Total quantity) while the conditional formatting do it Vertically this is the problem. You introduced a diverging case (60%) similar to me by comparing the current cell in the same line by the last column (Total) in the same horizontal line,  I need to do same but between each cell of (Total Quantity) (Q(n) vs Q(n-1)) I hope the Idea clear.