DAX sub-total wrong when filtered by other filter

Question

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.

Supposing [Volume] is a measure, how is it being calculated? I see you have only one table with all your data, add additional details about the columns and applied filter in order to reproduce your issue. — alejandro zuleta
Alejandro, thank you for looking at this. I have changed the post, added a data sample and more illustrative results. Unfortunately I'm not allowed to post actual pictures, so you have to click on the link. I hope it's a little better - I spent 2 hours preparing this example :-)) — Lana B
Your measure uses All() against the Brand, the Sub-Brand and the SKU. This is telling the calculate to ignore the filters applied to those fields, so your Pivot 3 subtotals are ignoring the Brand filter and your Pivot 4 totals are including the figures for both brands in each total. — Gordon K
Gordon K, i sort of understand that - but I don't know how to make it work the way i need. Any tips on how to change that? Thank you! :) — Lana B
Guys, the first problem - how to make it sum up only visible rows, regardless why the others may not be visible? Second, how to make brand sub-totals to include also only visible rows? — Lana B

Lana B Lana B · Accepted Answer · 2016-10-03T13:13:13

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!

DAX sub-total wrong when filtered by other filter

1 Answers