Forum Discussion

Alisea_MI's avatar
Alisea_MI
Resolver II
4 years ago
Solved

Filter Context in a calculation group

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...
  • Alisea_MI's avatar
    4 years ago

    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.