I have a pivot table where I will display 2 columns: country and store, and one value: sales. When the columns are not collapsed, I will display sales for all stores. However, when “store” column is collapsed, I need to sum the sales based on certain condition, for example, only sum sales from stores that are “flagship” stores. The issue I am having is that I can’t use filter in this case, as this would display only sales from “flagship” stores when “store” column is not collapsed. Is there anyway to implement this in pivot table?
Hello @Bruton3 Hope this message finds you well!!
If I understand you, to implement this logic in a pivot table, you can use Level Aware Aggregation (LAA) to control how data is aggregated based on the expansion or collapse of columns.
My suggestion is:
- create a calculated field that checks if the store is a “flagship” and returns sales only for these stores using a formula like
ifelse({Store Type} = 'Flagship', {Sales}, 0)
, which creates a field containing sales only for flagship stores. - use a level-aware aggregation calculation to sum sales from flagship stores only when the “store” column is collapsed by creating an additional calculated field that checks the aggregation level:
sumOver(ifelse({Store Type} = 'Flagship', {Sales}, 0), [{Country}])
which sums sales from flagship stores at the country level. In your pivot table, use the level-aware aggregation calculated field to display sales when the “store” column is collapsed, and display normal sales when the “store” column is not collapsed. This solution allows you to control data aggregation based on the column collapse condition without using filters that would affect data display when the “store” column is not collapsed.
Tell me if it’s help!!
Hi @lary_andr, thanks for responding, however this didn’t work. This would sum sales even if store column is not collapsed. However, I need to show sales at store level when store column is not collapsed. The reason is that only sales from stores meeting certain conditions will be counted, however we still want to see all the stores under each country when store column is not collapsed. The only thing I can think of is to use two separate tables, but would love to know if there is a way to achieve this in one table.
Hello @Bruton3, If I understand well, you need to display sales for all stores when the “store” column is not collapsed, but only sum sales from “flagship” stores when the column is collapsed.
My suggestion is: create a calculated field to identify sales from “flagship” stores like this
ifelse({Store Type} = 'Flagship', {Sales}, 0)
.
This field will contain sales only for flagship stores.
Then, use LAA to sum these sales at the country level when the “store” column is collapsed with: sumOver(ifelse({Store Type} = 'Flagship', {Sales}, 0), [{Country}])
In your pivot table, apply this LAA calculated field to display sales when the “store” column is collapsed, and show regular sales when it is not. This approach allows you to manage data aggregation based on the column’s collapse state without using filters that would affect the display of data when the “store” column is expanded.
Please, tell me if it’s work.