Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I wanted to compare the multiple measures of dimension and create new column with data like if all the measures are same then red else no colour.
for eg:
Week | Date | Name1 | Name2 | Name3 | Delta |
1 | 1/2/2023 | 100 | 100 | 100 | |
1/3/2023 | 100 | 2 | 100 | red() | |
1/4/2023 | 100 | 100 | 100 |
Here Week and Date will be Row
Name will be column
sum(sales) would be measure
Delta is the extra column we need to create to compare measures of name1,2 and 3
Sorry, I don't get it, I don't see how you can compute the delta.
However, I don't see neither, why you wouldn't be able to get the color if you are already getting the number.
And the easiest is to give a Label to you measure (like "Delta") and just use:
If("Delta"=0,green(),red())
I mean I used Calculated dimension for Delta for now. But main problem here is I wanted to highlight if the Sum(Sales) are different.
Assuming this is a pivot with an unknown number of Names, you'd need to do something with aggr() as a calculated dimension here. You can't have it be a measure as by definition Delta would be different than Name, meaning you would never have a full identical match.
Something like this should probably work or at least be adaptable to what you want (Note - in my case some values are null, but this shouldn't be an issue for you since you seem to always have values):
=aggr(if(Count(Distinct Aggr(Count(Dim3),Dim1,Dim2))>1,'Red'),Dim1)
Or is right.
That's why you should try to have a look to add a sub total and compute this delta there (unless you want to be able to do a selection)
We can ignore the delta part as that is the secondary requirement. I wanted to highlight the rows even if one of the SUM(Sales) are different
I am using the below query to highlight but few rows are not getting highlighted in the right way
=IF((SUM(AGGR(({<Name={'A'}>} Sales),Week,Day,Sales))=SUM(AGGR(({<Name={'B'}>} Sales),Week,Day,Sales))
AND SUM(AGGR(({<Name={'B'}>} Sales),Week,Day,Sales))= SUM(AGGR(({<Name={'C'}>} Sales),Week,Day,Sales))),green(),red())
If Its different all cells of same row in red and if its same all cells in same row in green
// Intermediate measures
SameSales_A = SUM({<Name={'A'}>} Sales);
SameSales_B = SUM({<Name={'B'}>} Sales);
SameSales_C = SUM({<Name={'C'}>} Sales);
// Highlight expression
=IF(
SameSales_A = SameSales_B AND SameSales_B = SameSales_C,
green(),
red()
)
Sorry, could you please help with intermediate measure, means the variable ?
anyone can help here pls
For example with this dataset:
Table1:
Load year(DATE) as YEAR, * inline
[
DATE,TYPE,VALUE
31-12-2022,A1,10
31-12-2022,A2,10
31-12-2022,A3,15
10-04-2023,A1,23
10-04-2023,A2,13
10-04-2023,A3,3
13-02-2023,A1,55
13-02-2023,A2,55
13-02-2023,A3,55
14-01-2023,A1,10
14-01-2023,A2,90
14-01-2023,A3,30
10-04-2022,A1,45
10-04-2022,A2,45
10-04-2022,A3,45
15-02-2023,A1,3
15-02-2023,A2,23
15-02-2023,A3,63
];
You build a pivot table with YEAR,DATE for the rows, and TYPE as a column (with sub totals).
Then you put this measure:
=If(SecondaryDimensionality()>0,Sum(VALUE)
,Min(Aggr(Sum(VALUE),TYPE,DATE,YEAR))=Max(Aggr(Sum(VALUE),TYPE,DATE,YEAR)))
And you obtain something like this: