Highlight measure values comparing across dimension value in Pivot table

[sumeet-vaidya1]

Hi Team,

We have a requirement wherein we need to compare the expression values and color the same.

For Eg. Below data set has 5 dimension and 1 measure calculated in a native pivot table. Out of 5 dim, 2 of them are added in rows and other 3 in the columns. We need to compare Quote1, Quote 2 expression value with Quote (Existing) dimension. If there is a difference in the value, that cell needs to be highlighted and if the value is same then no color highlighting is required.

I have attached sample data from which below pivot has been generated. Kindly assist.

Regards,

Sumeet

[rodrigo_martins]

Hello,

Put this expression as the background color of your measure:

if([QuoteDetails]<>'Quote (Existing)', //1st columns (Quote Existing) ignored (mantain white)
if(Sum(Amount) <> Sum({<[QuoteDetails]={'Quote (Exisiting)'}>} TOTAL<TierName, Country> Amount), //compare current Amount with first columns (Quote Existing) Amount
if([QuoteDetails]='Quote1', Red(), if([QuoteDetails]='Quote2', Blue())))) //if equals, paint Red column Tier1 and Blue column Tier2

You can replace the color functions (Red(), Blue()) with hexadecimal codes ('#80FFFF', '#FFFF80').
For more information about the how the TOTAL modifier works, I recommend this post here. 

Hope this helps.

[sumeet-vaidya1]

Hi Martin,

Thank you for your response...!!!

Values in "Quotedetails" column will be dynamic so we wont be able to hard code any dimension values.

[rodrigo_martins]

So this will depend on how the "Quotedetails" dimension is ordered in the Pivot Table. If there is no ordering, the column background color expression can be modified to:

if([QuoteDetails]<>'$(=FirstSortedValue(ALL QuoteDetails, ID))',
if(Sum(Amount) <> Sum({<[QuoteDetails]={"$(=FirstSortedValue(ALL QuoteDetails, ID))"}>} TOTAL<TierName, Country> Amount), Red()))

If it is in ascending or descending alphabetical order, instead of FirstSortedValue, the MinString and MaxString functions must be used. I included the ID parameter in the spreadsheet load, but it is just a call to the RowNo() function.

Doing it this way, the SetAnalysis expression will always compare the current QuoteDetails value with the value of the first column of the table (which is what I understood to be the requirement of this problem).

As for the background colors of each Quote, I would suggest creating a variable or a table for this, avoiding more complex logic. In the case of a table, it would look like this:

In the load script:

QuoteColors:
Load * Inline [
QuoteDetails, QuoteColor
Quote1, #FF8080
Quote2, #80FF80
];

In the table:

if([QuoteDetails]<>'$(=FirstSortedValue(ALL QuoteDetails, ID))',
if(Sum(Amount) <> Sum({<[QuoteDetails]={"$(=FirstSortedValue(ALL QuoteDetails, ID))"}>} TOTAL<TierName, Country> Amount), Only(QuoteColor)))