Weird pivot table behavior

[LesJean]

Hello everyone,

So I'm displaying information inside a pivot table using 2 dimensions. When I have multiple results I get the display I want, but when there is only 1 result, the pivot table displays the information in a weird way. Here's a screenshot of what I'm talking about.

As you can see, when there are multiple results for a section, the title row is displaying a "-" in each column. But, when there is only 1 result (such as the "Produits divers" section, indicated by the red arrows), the title row is displaying the same information as the row below it, so the data is displayed twice in the pivot table. Is there anyway to change this behavior to match the behavior when there are multiple results?

I'm using the Vizlib Pivot Table but I'm getting the same behavior using the standard pivot table.

Thank you,

LesJean

[Oleg_Troyansky · Accepted solution]

Hi LesJean,

It would help to see your Measures. I can only guess that one of the two is happening:

1. The measures have IF() conditions that test the value of the dimension. At the total level, when multiple values of the dimension are available, the IF() cannot be validated and returns NULL.

2. The Measures contain "naked" fields with no aggregation functions - for example, instead of sum(Amount), the measure has Amount alone ("naked"). In this case, the default aggregation function is only(), and it returns a null when multiple values are available.

Recommendation: 

1. Always use aggregation functions in your measures.

2. If the measure has to be dependent on the value of the Dimension, then you can use the function Dimensionality() to determine the current level of aggregation and modify your expression accordingly:

IF(Dimensionality() = 2, .... one calculation, .... another calculation)

cheers,

Oleg Troyansky

www.naturalsynergies.com

https://MastersSummit.com

[LesJean]

---

IF(Dimensionality() = 2, .... one calculation, .... another calculation)

---

That's exactly what I needed! I was able to return '-' if the dimensionality wasn't 2. Thank you so much!