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 GarethW ,
Wasn't aware that you had the percentage of column total would though it was a summarization or a measure, you need to change you values in the matrix by the measure below and then use the formatting as refered previously:
Percentage of colum = CALCULATE(COUNT('Table'[Product Type]))/ CALCULATE(COUNT('Table'[Product Type]);ALL('Table'[Product Type]))
Regards,
MFelix
Thank you MFelix that is making progress, but I still can't get the conditional to work.
I'll give a bit more detail:
My raw data is rows of customer data, each row being a purchase, which has a date, region, etc.
The initial matrix counts the customer ID, by region and month:
If I then create the measure, it correctly converts the above in to 'percentages' of each column
But the conditional formatting still doesn't compare each row & column against the total for the row.
I need each cell for say Europe to only be conditionally formatted against the total for Europe (47.75%) and ignore all the other regions. Then each Europe North cell to be formatted vs 10.01%, etc.
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.
Thank you so much MFelix , Got it I'll try it by tomorrow cuz I'm now back home and if it works I'll give you my thumb👍
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
Hi MFelix This is the screen-shot.
Hi MFelix
I was wondering if its possible to change the color for the "ROWS". I mean in your example should be: "Region".
I have this:
and i want to change the color of the rows (I mean the text in the first column, A) 0-15Days.... etc.
Im using matrix chart, if i use table chat, will work, but i need use matrix.
in ther words, i need that the firs row, should be totally green, the secong row yelow and etcHi Noredlac86 ,
In this case you need to add an additional statment to your calculation in order to check if you are in the line of A, B or C.
You need to create a measure similar to this:
Color coding by row = SWITCH(SELECTEDVALUE('Table'[Region]), "A", "Green", "B", "Blue")Then on the condittional formatting select the Field value
Result below:
You can also use the HEX codes for the coloring.
I have similar problem with different scope, I think till now there is no solution for this.
