Help on Context filter

[apai1990]

Hello, 

I'm migrating the Tableau dashboard to Power BI, data does not seem to match for the same period. After some digging in the Tableau dashboard, I found there were 13 unique EMP IDs but in Power BI there are 17. On further inspecting I found the Tableau period filter is in context and if I remove the period filter from context my number matches with that of Power BI.

So the period filter in Power BI I'm using is not working as it should as a context filter, as far as I know, all the filter in Power BI works on a contextual basis.

I need to know where I'm going wrong.

My data consists of EMP IDs with average pay against them, I'm also applying another filter named as "Global Condition-Hours" to filter out EMP IDs whose total working hours are less than 1600.

The formula for Global Condition-Hours and Average pay is below:

Global Condition-Hours = CALCULATE(SUM('Final Table'[Total Hours]), FILTER(ALLEXCEPT('Final Table','Final Table'[Emp Id]),'Final Table'[Time Range]))>=1600

Avg. Pay = SUM('Final Table'[Total Compensation])/DISTINCTCOUNT('Final Table'[Emp Id])

Screenshot for Power BI:

 Screen Shot from Tableau (here in Tableau the period filter is named "Time Range")

I think I should include the period filter in Global Condition-Hour & avg pay, but I'm not successful in doing so,

any help will be highly appriciated

[halfglassdarkly]

You should be able to. Try including [Time Range] and [Global Condition-Hours] as fields in your visual table. What values do they show when inheriting context from the table row and slicer?

Also, with your [Avg. Pay] measure, should it be dividing by count rather than distinct count? The distinct count of Emp Id is always going to be 1 at row level in your visual table if you are including Emp ID in the table, so you're just dividing by 1?

[halfglassdarkly]

Did you mean to include the 'Final Table'[Time Range] inside ALLEXCEPT() rather than as a seperate filter? You could try:

Global Condition-Hours = CALCULATE(SUM('Final Table'[Total Hours]), ALLEXCEPT('Final Table','Final Table'[Emp Id],'Final Table'[Time Range]))>=1600

[apai1990]

I tried using time rage in ALLEXCEPT like this:

Global working hours= CALCULATE(SUM(table[working hours]), ALLEXCEPT(table, table[Emp Id], table[time range]))>=1600

But the result is blank, I know that the Power BI is calculating it on each time range. Which is cumulatively less than 1600. The problem is my time range is like 202201 where synatx is year+weeknum. I'm using it as a filter range means between the range I need my EMP ID count which will be used to calculate Avg. Pay

I think I need to devote more time to this problem...

[apai1990]

Thanks for the reply, I tried your solution but it has no affect on the visual the result is the same. I wanted to know why I cannot use time period as a context filter