Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am able to get the % to total values to display properly in my pivot table. Now, I would like to display the following background color formatting as it is in Excel. ([PS Department Group] and [KPI Month] (going across) are both dimensions.
Red where the % for individual month is less than TOTAL % for all months.
Green where the % for individual month is greater than TOTAL % for all months.
Any help is greatly appreciated!
Thanks,
Dan
Hi,
The sintaxe "RANGESUM(Before(SUM(Value),0,ColumnNo() ) )" return the value acumulate for each column:
30 65 30 -> 30 95 125
The function "LAST()" replicate the last value for each row: 125
COUNT(Total Distinct Date): returns the number of month (column) because we need to calculate the avg for all value in the table:
"41,666666"
After just need to verify the valeu with this result to demonstrate the color that you want:
IF 30 > 41,666666 return GREEN()
IF 30 < 41,666666 return RED()
What is your expression? How is the chart defined?
If(GetCurrentField([Date Comparison])='KPI Event Month'
sum({<KPI={'Visit Encounters'},
[KPI Event Year]={$(=Only([KPI Event Year]))}>}[KPI Amount])/
sum({<KPI={'Visit Encounters'},
[KPI Event Year]={$(=Only([KPI Event Year]))}>}TOTAL<[KPI Event Month],[KPI Forecast Unit Short]>[KPI Amount]),
If(GetCurrentField([Date Comparison])='KPI Event Quarter',
sum({<KPI={'Visit Encounters'},
[KPI Event Year]={$(=Only([KPI Event Year]))}>}[KPI Amount])/
sum({<KPI={'Visit Encounters'},
[KPI Event Year]={$(=Only([KPI Event Year]))}>}TOTAL<[KPI Event Quarter],[KPI Forecast Unit
Short]>[KPI Amount])))
Apply your conditions as Background Colour, you find it under Properties - Expressions and then open the small "+"-sign.
HTH
Peter
Peter,
I know how the colored background works...What I need help with is getting the colors to show up based on each month's % to Total compared to ALL month's % to Total.
Thanks,
Dan
you can use the functions Dimensionality() OR ColumnNo() to calculate the Column total for each Column.
Ex.: IF(Dimensionality() <> 0, SUM(Values)/100, <new_expression>)
Dimensionality() and Column() will not help me, because the Month going across is actually a dimension. (There is not a separate expression for each month.)
I understand but the function dimensionality() return the value "0" when the column is "TOTAL"
You can use to calculate the total for each row. See this example;
Eduardo,
I can see how this has the potential to work. Now I know the difference between Column() and ColumnNo() and how it can be used. Thanks!
Now I just need to figure out how to apply it to my issue.
If you could help decipher what the following expression is doing, it would be a great help.
IF(Sum(Value) < Last(RANGESUM(Before(SUM(Value),0,ColumnNo()))) / COUNT(Total Distinct Date), RGB(255,0,0),
IF(Sum(Value) > Last(RANGESUM(Before(SUM(Value),0,ColumnNo()))) / COUNT(Total Distinct Date), RGB(0,255,0)))
Hi,
The sintaxe "RANGESUM(Before(SUM(Value),0,ColumnNo() ) )" return the value acumulate for each column:
30 65 30 -> 30 95 125
The function "LAST()" replicate the last value for each row: 125
COUNT(Total Distinct Date): returns the number of month (column) because we need to calculate the avg for all value in the table:
"41,666666"
After just need to verify the valeu with this result to demonstrate the color that you want:
IF 30 > 41,666666 return GREEN()
IF 30 < 41,666666 return RED()