Show categories which average of certain values is greater than overall average

[danextian]

Hi All,

I have a

- table that has these fields:
Department

Cost Center

OT Paid

Measure for sum of OT Paid

-a table that shows total headcout for each Cost Center (with relationship to the Cost Center field from the previous table)

-and another thable that shows to the total without any relationship at all with the two the previous tables

I created these measures:

Ave OT Paid = Divide( Sum OT Paid, Sum of Headcount) - Headcount from table 2

Ave OT Paid Overall  = Divide(Sumx(All(Table1[Cost Center]),Sum of OT Paid),Sumx(All(Table3[Cost Center]),Sum of Headcount)) - headcount from table 3

OT Paid off CC with Ave Greater Than Overall Ave OT = Calculate(OT Paid, filter(All(Table1[Cost Center]),Ave OT Paid > Ave OT Paid Overall))

OT Paid off CC with Ave Greater Than Overall Ave OT would show the correct value if used in a card. However, if used in a table, would show the same value for each row instead of just showing those cost centers with OT Paid/Headcount  is greater than the overall OT Paid/Headcount, and the corresponding OT paid.

[v-sihou-msft · Accepted solution]

@danextian

In this scenario, you have applied the filter context "Ave OT Paid > Ave OT Paid Overall", this filter context is for your calculation only. You can't have it impact the row context in a table visual. If you want to filter those cost centers in your table visual. You can add a tag field like:

Tag = IF(Ave OT Paid > Ave OT Paid Overall,1,0)

And use this column in visual level filter or slicer to filtering rows.

Regards,