Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Understanding Context Between Average and Sum - Remove Filters

Hello,

 

I am trying to resolve one issue, where I want to remove a context from my dax. I tried ALLEXCEPT but didnt' work.

Here is the example:

1. Person A got 2 pay changes (11.1 % and -10%) so effectively he got 1.1% pay increase. So Avg. % pay change is 1.1%

2. Person B got 1 pay change (25%) so avg. for him is 25%.

 

Now Avg. for both of them is (1.1 + 25)/2 = 13%.

 

So bascially, I want to remove "Pay Action Reason Text" from the context so it calculates at the higher level.  I think I can use "REMOVE FILTER" but as per my formula it is averaging first so not working. I have put in the file below.

 

Avg. of 2 People:

 

 

https://drive.google.com/file/d/12rCoPAF25pjw6f-GmpnVK9c0WP7gRi1t/view?usp=sharing

 

Thanks,

RK

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    Is this what  you want?

     

    I didn't do much correction, just divide the two measures you've created.

    Avg2. % Pay Changes = DIVIDE(HRIS_TD_COMP_BY_NETWORK_SUMMARY[Sum % Pay Changes],[# of EE Receiving Pay Changes])

     

    Best Regards,
    Community Support Team _ Eason

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    " A got 2 pay changes (11.1 % and -10%) so effectively he got 1.1% pay increase"

     

    Not really.  Let's assume A starts with 100.  100 x 1.111 x 0.9 = 99.99 .  He got a decrease, not a raise.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      You are absolutley right. We are having internal discussion about exactly this topic. My question is: if we go through this use case, then how can we achieve the desired result? Thanks!

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        I would start with a better definition of the business problem - what issue are you actually trying to solve?

        Then I would cleanup my data, and provide good timestamps

        Lastly I would work with the business users on the UX. They often cannot articulate well what they want but when you show them a prototype they can quickly tell why what you did is not what they wanted. After a couple more iterations you will hoepfully arrive at an acceptable solution.

         

        The first step is absolutely crucial.