Forum Discussion
Conditional formatting of Matrix based on Total average
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.
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.
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.
- 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. 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.
Sorry, MFelix I didn't Understand your answer could explain with some details?
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?
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.