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 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.

MAAbdullah_47's avatar
MAAbdullah_47
Helper V
6 years ago

Just to explain my case, please look at the screen-shot below:

 

Total Quantity = SUMX(Sales,DIVIDE(Sales[Order Quantity],RELATED(Products[Converting Index]),0))

And another Measure to calculate the difference between each quarter and the preceding one as the following:
QTY Quarter Difference = [Total Quantity]-CALCULATE([Total Quantity],DATEADD(Dates[Date],-1,QUARTER))
There is a date table In relationship with (1:m) between Date table and sales table, I need to do a color diverging for (Total Quantity) measure  start from the lowest (high red) and (yellow in between) and (high green for the maximum),

Please let me know if you solution fit with the case above on my thread from two perspectives:
1-Dax expression.
2-Use (1) in the conditional formatting.

Thank you  

 

 

  • MAAbdullah_47's avatar
    MAAbdullah_47
    Helper V
    6 years ago

    Hi MFelix  This is the screen-shot.

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      Hi MAAbdullah_47 ,

       

      You want to base the conditional on the Total quantity? for the first line all values compare with 149105.32 is this correct?

       

      Can you share a sample file?

       

      If there is sensitive information you can send it trough private message, or use a mockupfile.

      • MAAbdullah_47's avatar
        MAAbdullah_47
        Helper V
        6 years ago

        Thank you MFelix  Actually not, I mean to compare Total quantity(TQ) each quarter No with the preceding Quarter (Q(n) vs Q(n-1)) here is an example (refer to the screen-shot) in the first line of the matrix for the customer routs:

        In Q1 = 37,722.68

            Q2 = 38,859.33

            Q3 = 39,038.00

            Q4 = 33,484.75

         

        The color diverging should be something like this (Horisintal compare not vertical compare):

        Q1 (green +)

        Q2(green ++)

        Q3(green +++)

        Q4(maybe red).

         

        For the data, It is too confidential but I think the structure is common in Retail Industry Please look at the below 
        Screen-Shot:

         

        For the setting of the matrix here is the setting:

        For the conditional formatting here is the screen-shot that I would like to be :

         

         

        For the measures, I sent it In my previous post, I hope the Case is more clear now.

        Again thank you MFelix  for your help and support.