CODEWITHSUNDEEP
excel-2013powerpivotdax
Lana B
Lana B

Reputation: 496

DAX sub-total wrong when filtered by other filter

I have a power pivot summarising sales data on various levels of hierarchy.

I have added a picture showing data structure and expanded examples to be more illustrative.

The data has products and countries.

PICTURE WITH SAMPLE OF DATA

My task is to return a "category total" of sales, which should respect all geographical filters, but ignore product filters.

This is the DAX I am using 

Sales Total:=SUM([Volume])

(this measure is an explicit sum of original volume data field)

Category Totals:=
       CALCULATE( [Sales Total], 
                  ALL(TBL[brand], 
                      TBL[Sub-brand], 
                      TBL[SKU]
                     )
                  )

this is the measure where I'm trying to capture totals above product level - called "Category" because it sums up all products in a geographical set, be it a market, region, sub-region.

PICTURE WITH MY RESULTS:

Problem 1: when geographical field is filtered indirectly, sub-totals do not reflect that (i.e. market doesn't have for Brand 2).

Problem 2: if a product attribute (i.e. Brand) is higher in hierarchy of row fields than geographical (i.e. market etc), sub-totals on that level show a global total at all times instead of sub-totalling regions/markets that they belong to.

Upvotes: 0

Views: 788

Answers (1)

Lana B
Lana B

Reputation: 496

I spent a weekend trying different combinations of various functions, and it appears, I needed to do is use either

NEW CAT TOTAL:= SUMX(VALUES(TBL[Market], [Category Totals])

where 

Category Totals:=  CALCULATE( [Sales Total], 
              ALL(TBL[brand], 
                  TBL[Sub-brand], 
                  TBL[SKU]
                 )
              )

or adjust the Category Totals to an equivalent by adding VALUES of Market

Category Totals:=  CALCULATE( [Sales Total], 
              VALUES(TBL[Market],
              ALL(TBL[brand], 
                  TBL[Sub-brand], 
                  TBL[SKU]
                 )
              )

I don't understand why these work, but they do. I am grateful everyone who tried to help!

Upvotes: 1

Related Questions