Hide measure values for a specific dimension

[Amit_B]

Hi,

I have a pivot table where users can dynamically choose which dimensions to display - ItemCategory, ItemGroup, ItemID.
One of the measures in the pivot is ItemTarget, which is defined at the Category and Group level.

The problem is that when the pivot is expanded to the item level, the target get repeated for every item. The values in each row are not included in the total value, but each row get the target value for its category & group.

How can I hide the targets at the item level, so that it only appears at the category ang group levels, even when users choose to display the item dimension?

[pablolabbe]

can you share an image of the pivot table as an example to understand your issue?

[marcus_sommer]

You may query the dimensionality() to branch between the category/group and id-layer.

Another approach could be to distribute the category/group-values to the id-level, like:

group-value / count of id as atomic-target

More simple - remove the id-level from the chart and use separate charts with an appropriate granularity.

[Nagaraju_KCS]

Try this

The solution is to use the dimensionality() function within a conditional expression in your ItemTarget measure. 

Assume your dimensions are in this hierarchy

ItemCategory (Dimensionality = 3)

ItemGroup (Dimensionality = 2)

ItemID (Dimensionality = 1)

If you have a row showing Category A, Group B, and Item 101, the dimensionality for that cell is 1. If you are looking at the Group B Total, the dimensionality is 2.

=IF(Dimensionality() <= 2, Sum(ItemTarget), Null())

[Chanty4u]

Try this 

IF( GetSelectedCount(ItemID) = 0, ItemTarget )

Or

May be 

IF(Dimensionality() < 3, ItemTarget)