topic Re: SecondaryDimensionality doesn't work as expected with "Show column if " in pivot table in App Development

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

dana — Fri, 12 May 2023 08:05:18 GMT

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!

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

Kushal_Chawda — Fri, 12 May 2023 09:20:07 GMT

@dana what exactly you want to do

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

dana — Fri, 12 May 2023 10:23:14 GMT

Hi @Kushal_Chawda ,

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

Thanks!

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

Kushal_Chawda — Fri, 12 May 2023 11:43:43 GMT

@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), )

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

dana — Fri, 12 May 2023 13:22:13 GMT

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.

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

dana — Fri, 12 May 2023 13:53:21 GMT

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!

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

Kushal_Chawda — Fri, 12 May 2023 13:55:23 GMT

@dana try below expression

=sum(total <Customer> additional_cost)

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

dana — Fri, 12 May 2023 14:23:46 GMT

It doesn't work for me..

I will adapt the script.

Thanks for your kind help!