Hi Dna Community,
In the below example i am trying to build a conditional formatting in a matrix table for the Total sales for each year and each customer based on higher sales the color should becomes darker , plan to achieve this using a measure( Sales Rank) measure created into a conditional column(field value). My measure does not seem to work .
Forecasting.pbix (543.7 KB)
Hello @Unni,
Thank You for posting your query onto the Forum.
I’m not sure why you’re trying to use the “Sales Rank” measure when the same results could be achieved using the “Total Sales” measure.
1). Click onto the down arrow which is visible onto the “Total Sales” measure.
2). Select the “Conditional Formatting” option.
3). Under that select the “Background Color” option and set the color schemes for the “Minimum” and “Maximum” value.
Below is the screenshot of the final results provided for the reference where numbers get darker and darker for the higher sales amount.
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
Forecasting - Harsh.pbix (543.2 KB)
Hello @Unni,
Also there were quite a few issues as well with regards to the “Sales Rank” measure that you had written and the fields that you had dragged inside the Matrix visual.
So firstly, you had dragged the “Date” field from your Date table and from that hierarchy you deleted the “Date”, “Month” and “Quarter” levels and retained the “Year” level. The first step itself was not correctly followed here. You got to mark your “Date” field as a “Date Table” rather than using it as a hierarchy. You can directly make use of “Year” field instead rather than following the former approach.
Once this is done, you’ll also have to correct your “Sales Rank” measure. The measure that you’ve written is firstly, for the “Year” context only and that too not correct. Below is the measure for the “Yearly Sales Rank” provided for the reference -
Yearly Sales Rank =
RANKX(
ALL( Dates[Year] ) ,
[Total Sales] , ,
DESC )
But based on this measure you’ll not be able to evaluate the results correctly for the Customer Sales at individual Year level. For that you’ll have to insert the context of Customers inside your measure. Below is the revised measure of the Ranking provided for the reference -
Ranking =
IF( ISINSCOPE( 'Customer Data'[Customer Names] ) ,
IF( ISBLANK( [Total Sales] ) ,
BLANK() ,
RANKX(
ALL( 'Customer Data'[Customer Names] ) ,
[Total Sales] , ,
DESC ) ) ,
BLANK() )
Once this measure is written, then by following the steps which were suggested above in the previous post. Now, you’ll be able to conditionally format them based on Ranking. Below is the screenshot of the final results provided for the reference -
(Note:- In this case, the color scheme for “Minimum Value” will be set to darker since lower the rank, higher the sales value and vice-versa)
I’m also attaching the working of the PBIX file as well as providing few of the links below of the videos on the “Ranking” topic from our EDNA channel for the reference purposes.
The Page One in the PBIX file shows “Conditional Formatting By Total Sales” whereas Page Two shows “Conditional Formatting By Ranking”.
You can go with either of the scenario that suits your business circumstances or analysis.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
Forecasting - Harsh v2.pbix (536.5 KB)
Hi @Harsh ,
Thanks for the quick indepth reply and pointing out big mistake on the date selection, and all the additional resources , i believe i did not state my requirements clearly but with your guidance i have been able sort it out , i needed the rank each year for each customer as shown in the below screenshoot.
Hello @Unni,
You’re Welcome.
I’m glad that I was able to assist you on your query.
Thanks and Warm Regards,
Harsh
