Filters isn't working on Calculated Column Cross Table

[Shri G]

Hi All,

Please find attached sample data. I have to create cross table for Travel and Work hours based on type CR and PM 

"Site","Type","Work Hrs","Travel Hrs""L102","CR","1.5","1.25""L102","CR","1.75","1""L102","CR","1.5","1""L102","CR","7.5","2""L102","CR","1","1""L102","CR","1.25","2.75""L102","CR","3.75","2.25""L102","CR","1","0""L102","PM","2","4""L102","PM","7.5","2""L102","PM","1","1""L102","PM","1.25","2.75""L102","PM","4.24","4.75""L102","PM","3.75","2.25""L102","PM","1","0""L102","PM","1.5","1"

I have created calculated columns

PM Work Hrs:

case WHEN [Type]='PM' THEN Sum([Work Hrs]) OVER ([site]) END

PM Travel Hrs:

case WHEN [Type]='PM' THEN Sum([Travel Hrs]) OVER ([site]) END

CR Work Hrs :

case WHEN [Type]='CR' THEN Sum([Work Hrs]) OVER ([site]) END

CR Travel Hrs:

case WHEN [Type]='CR' THEN Sum([Travel Hrs]) OVER ([site]) END

These calculated values are giving me the correct output, these cross table values are changing for all filters except hierarchy filters. I have created Date and Geography filters. For these filters whenever I select/Deselect values, that is not reflecting in Cross table

Can somebody please help me with this?

Thanks in advance!

[Jose Leviaguirre]

Try to use intersect on all your expressions. For example:

Sum([Work Hrs]) OVER (Intersect([site], All([Type])) WHEN [Type] = "PM")

[Shri G]

Hi Jose,

Thank you so much for your reply.

This expression isn't working, getting 'Expected ) but found WHEN on line 1' error. I tried with syntax as well

[Fredrik Rosell]

Hello,

"I have to create cross table for Travel and Work hours based on type CR and PM"

Just to confirm something very basic first - do you need to use Calculated columns or can you just split your cross table to show what you need, using custom expressions on the axes where needed? Added a fake L103 Site to get some additional data.