Understanding Context Between Average and Sum - Remove Filters

[RK_JOB]

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

[lbendlin]

" 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.

[RK_JOB]

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!

[v-easonf-msft · Accepted solution]

Hi, @RK_JOB 

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

[lbendlin]

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.