Solution: Beast Mode & Pivot Table Aggregation Error

JedP

Use Case:

This chart uses a Beast Mode code for the Rows and Values in a pivot table chart. The results provide an error that is hard to understand without knowing what is causing the issue. Based on the results, we can also see the Cases is not calculating as expected.

The dataset only has 5 rows of data.

The Rows column in the pivot table is using a custom Beast Mode code.

CASE

  WHEN Material Number = '6' THEN 'Cases'

  ELSE 'Gallons'

END

The Values values column in the pivot table is also using a custom Beast Mode code. The CASE statement has multiple FIXED functions which causes the card to aggregate to the dimensions in the card and not the Row Group column as intended.

CASE

  WHEN Material Number = '6'

  THEN SUM(SUM(Cases)) FIXED(FILTER DENY Material Number)

  ELSE SUM(SUM(Gallons)) FIXED(FILTER DENY Material Number)

END

The error message also had a hint at the same problem. It states that "Multiple results encountered for the same location". When we change the card type to a table card, it appears that the Gallons section is not aggregating up.

Adding the Material Number helps us see that the card is aggregating, and confirms the card is not using the Row Group column to aggregate. This causes the card to aggregate to the dimensions in the dataset, in this case the card is using the Material Number to aggregate to.

To correct the card and display the correct values we need to do 2 things.

1. Correct the case statement logic to allow the FIXED function to use the custom Beast Mode as the default aggregation grouping. This is done by wrapping the case statement with the FIXED function instead of having multiple FIXED functions.
2. Add the BY condition to the FIXED function to set the aggregation grouping. Now that the same custom grouping is added to the code that grouping, the card will default to this grouping when aggregating.

SUM(SUM(

  CASE

    WHEN Material Number = '6' THEN Cases

    ELSE Gallons

  END)) FIXED(BY (CASE WHEN Material Number = '6' THEN 'Cases' ELSE 'Gallons' END) FILTER DENY Material Number)

The new code now provides the proper results and aggregates on the custom Row Group column.

MichelleH

@JedP This is super helpful, thanks for sharing!