Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX filter

Hi,

 

I need to calculate the Sales of a team ignoring the Single salesperson selection but considering the team the person belongs to: 

In this case, I need the team sales to be the sum of ONLY France sales. I wrote the formula 

Team Sales = CALCULATE([Individual sales],all('Kam - team'[Kam]))
 
which is not considering the Kam filter, as I wanted, but it is returning the total sales of all of the teams and I need only France.
 
Thanks,

 

 

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Team Sales = CALCULATE(
    sum([Individual sales]),
    all('Kam-team'[salesperson]),
    allselected('Kam - team'[Kam]))

    Maybe something like this?

    Not sure if { all('Kam-team'[salesperson]),} is needed

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi wvdv,

       

      Thanks for your answer.

       

      Actually I have this dimension table:

       

      I need that  if the filter is Tiphaine Boehm team sales sum all of the sales when team = FR, if the filter is Tania Gracia then sum all of the sales with team = ES etc

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Okay this works in my test:
        Where table 1 has two columns: kam and team.
        Table 2 has two columns: kam and sales.
        I only recreated part of your table.
        Relationship is one to many from table 1 to table 2.
         
        Measure = CALCULATE(SUM(Table2[Sales]),
        ALL(Table1[Kam]),
        ALLSELECTED(Table1[Team]))
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Selecting in a variable and using is later should give

     

    Team Sales = 
 var _team= allselected('Kam - team'[Team])
 return
 CALCULATE([Individual sales],all('Kam - team'[Kam]),'Kam - team'[Team] =_team)
or
CALCULATE([Individual sales],all('Kam - team'[Kam]),'Kam - team'[Team] in _team)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may create measure like DAX below.

     

    Team Sales= CALCULATE([Individual sales], FILTER(ALLSELECTED('Kam - team'[Team]), 'Kam - team'[Team] =MAX('Kam - team'[Team])))

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous  ,

     

    Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy