topic Re: Highlight measure values comparing across dimension value in Pivot table in App Development

Highlight measure values comparing across dimension value in Pivot table

sumeet-vaidya1 — Fri, 15 Nov 2024 20:55:05 GMT

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

Re: Highlight measure values comparing across dimension value in Pivot table

rodrigo_martins — Fri, 12 Jul 2024 13:15:48 GMT

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.

Re: Highlight measure values comparing across dimension value in Pivot table

sumeet-vaidya1 — Mon, 15 Jul 2024 06:52:49 GMT

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.

Re: Highlight measure values comparing across dimension value in Pivot table

rodrigo_martins — Mon, 15 Jul 2024 12:32:45 GMT

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)))