How to hide calculated values from cells but leave total

[#1]

In the script attached, in the column "Cost", each employee has a cost associated with her.

For presentation purposes, I would like to show blank cells and only show the total of 592,45. Is this possible?

Employee  Name	Total Employee Cost	Total Employee  Hours	Employee Hourly  Cost	Cost
EMP1	3.201,00  €	23,50	20,73 €
EMP3	1.072,93  €	1,75	14,80 €
EMP4	1.915,45  €	4,50	12,28 €
EMP6	2.409,94  €	1,50	16,07 €
Total	8.599,32  €	31,25	16,14 €	592,45 €

Thanks

[swuehl · Accepted solution]

Maybe like attached?

edit:

Cost expression:

=if( Dimensionality()=0,

sum(aggr(

sum(HoursWorked) *

sum({<EmpName={"=sum(HoursWorked)>0"}>}  EmpTotalCost)

/ sum({<Activities= , EmpId={"=sum(HoursWorked)>0"}>} total <EmpName> HoursWorked)

,EmpName))

)

and set an appropriate NULL representation string in presentation tab.

[#3]

Thank you. This did the trick for the particular example. So I mark it as a correct answer.

I have another case, where I want to have the same result, but instead, all cells become blank. Shall I post that particular example and open an new thread or there is an easy way to deal with the problem?

Thanks!

[swuehl]

If you tell us a bit more about your other case, I will try to help you with that, too.

But it's more important that you try to understand how above works (how the dimensionality() function could be used to tell if an expression is evaluated in Total context or not). If you are using a pivot table chart with pivoted dimension, you might need to look into secondarydimensionality(), too.

If you are using some different levels of aggregation, you might need to adapt the comparison to the right number representing the level, please check the Help, 'Examples of Chart Inter Record Functions'.

Instead of using an expression that only evaluates if the dimensionality() is right, you can keep your expression as is and use the expression attribute 'text color' (open the attributes in expression tab by clicking on the plus sign next to the expression label) to set the color to white:

=if(Dimensionality(),white())