SecondaryDimensionality doesn't work as expected with "Show column if " in pivot table

[dana]

Hi People,

I have added the measure Additional Costs with a conditional show expression to a pivot table: 

SecondaryDimensionality()=0

But the expression is displayed for each column as well (marked in red).

How can this be resolved?

Thanks in Advance!

[Kushal_Chawda]

@dana  what exactly you want to do

[dana]

Hi @Kushal_Chawda ,

I would like the measure Additional Costs to be displayed as a total only.

Thanks!

[Kushal_Chawda · Accepted solution]

@dana  If you create Additional Measure , it will be replicated across dimension and total enabled for that column, you can't restrict it with anyway. You need to create custom total column in this case so that single measure can be used to create the report

create inline table with two custom dimensions

Dim1- 1-Total, 2-Part
Dim2- 1-Cost1 (Dim1 Total), 2-Cost2,3-Additional Cost, 4-Cost1, 5-Cost2
Load * Inline [
Dim1,Dim2
1,1
1,2
1,3
2,4
2,5];

Create pivot table with below dimension
Row Dimension:
Customer

Column Dimension:
1) =pick(Dim1,'Total',PartColumn)
2) =pick(Dim2,'Cost1','Cost2','Additional Cost','Cost1','Cost2') 

Measure:
=pick(Dim2,
sum(cost1),
sum(cost2),
sum(additiona_cost),
sum(cost1),
sum(cost2),
)

[dana]

Hi @Kushal_Chawda ,

Thanks for the explanation and the brilliant solution!

The only issue I had was with the totals labels.

I cleared the Sort options for that dim, and it resolved the issue.

[dana]

Hi @Kushal_Chawda,

If I may, I have another question:

The Additional Costs are by customer level (not by part).

I get 0 when adding them to the table.

Do you have a tip for me?

Thanks! 

[Kushal_Chawda]

@dana  try below expression

=sum(total <Customer> additional_cost)

[dana]

It doesn't work for me..

I will adapt the script.

Thanks for your kind help!