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, thanks, but I have tried that and it doesn't work as there is no way for it to use the last 'total' column as a reference.
The actual matrix is based on counts, and the display type was just changed to be based on 'Percentage of Column Total'
What I need to be comparing in the heat map are the percentages being displayed, not the actual counts behind those, as the counts will vary depending on the month, I only care about the relative share for any given month, versus the over all share (which is that final 'Total' column)
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👍
I have similar problem with different scope, I think till now there is no solution for this.
