Filter Context in a calculation group

[Alisea_MI]

Hi!

I have been struggling with the issue below for several days now, and would really appreciate some expert input.

I have created a calculation group to show several calculated measure side by side in a visual.

My report has a drill through for navigation purposes (keep all filters turned off to able to use slicers on the last page otherwise they only show the choice made on page 1) between the following dashboards: Salesperson - > City-> Store-> Store Details.

On the "Store Details" page I am displaying  1) the growth of the store I have drilled to, 2)the growth of the whole region the store belongs to.

My issues is that a calculated measure loses filter context when used in a calculated group. However the same measure (with the same formula) works correctly when visualized in a separate chart side by side with the calculation group visual. Filters applied affecting these visuals are the same. The drill-through filer  on the page shows Sales Region (All).

Please see an issue example below.

The formula for my measure shows a growth for a chosen period for a region.

Growth QuantityRegion = CALCULATE([Growth Quantity], ALLEXCEPT(Customer,Customer[Sales Region]))

The calculated group dax code for the region is the same:

The correct region should by picked by the context since we have drilled us to the specific store.

In a separate visual, this formula shows the correct region with the correct data (please see 1 and the table in the pictures below).

In a calculated measure, this formula shows the total for all the regions (please see the 2 and the table in the pictures below).

How can I pass the region context to the calculated group? Or is calculated group meant only to show the grand total row?

THe links for a mock download that replicates the issues are here:

https://www.dropbox.com/s/g7u7kwkyf96fthq/Community.pbix?dl=0

https://1drv.ms/u/s!AsRPYONOO3kumErdiECMBpBNrTVr?e=WyJNv3

Thank you so much in advance for all the input on this!

[Alisea_MI · Accepted solution]

I have been working on this for a while and would like to share some of the findings and a solution in case anyone else would need to do the same.

I have two calculated group solutions: 1) one where I have  drill-through page, 2) one where I only have one page with the filters on this page.

My calculation group in located on a detail pagek which shows details for a certain store we have drilled us to. THe calculation group  shows growth for: store, city, chain, region and country.

In order to make the figures work, I needed to have drill-through pages for a sales person, city, chain and store. Note: if you skip any of these, e.g. Chain, the Chain figues on the detail page would not be correct.

The formulas I used in the calculated group were:

-Store: 

CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALL(Customer[Sales Region]), ALLSELECTED(Customer[Store City]), ALL(Customer[Chain Store]), ALL(Customer[Store Name]))

-Chain: 

CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALL(Customer[Sales Region]), ALL(Customer[Store City]), allselected(Customer[Chain Store]), all(Customer[Store Name]))

-Region: 

CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALLSELECTED(Customer[Sales Region]), ALL(Customer[Store City]), ALL(Customer[Chain Store]), ALL(Customer[Store Name]))

- Country: 

CALCULATE(SELECTEDMEASURE(), all(customer))

The order of elements in the formula makes difference.

[Greg_Deckler]

@Alisea_MI So what is your Calculation Group DAX code?

[Alisea_MI]

@Greg_Deckler It's the same as my separate visual code.  I hoped it would work as it was.

[Greg_Deckler]

@Alisea_MI Hmm, normally I would expect: CALCULATE(SELECTEDMEASURE(), ALLEXCEPT(Customer,Customer[Sales Region]))

Calculation groups in Analysis Services tabular models | Microsoft Docs

[Alisea_MI]

@Greg_DecklerI have tried it as well - it returns the same result.

[Greg_Deckler]

@Alisea_MI What is the formula for growth quantity? Can you post mock data to emulate this?

[Alisea_MI]

@Greg_Deckler  I have created some example file, that replicates the issues on page "Details". What I expect in the calculation group is this case is what I see in the separate visual for Region, 6%. 

The only way I managed to have to work is by turning the "Keep all filters on". 

Howeverm in this case I get another issue with the Period slicer. My user wants to be able to use it on the details page as well. And in the case when I have all the filters on , the slicer on the detail page is set to when is chosen on the 1st page. I have tried to change advanced settings on the slicer syns, however it hasnt worked.

Do you have any tips?
Thank you in advance!

Alisea