Dynamic Grade Column - aggregate by (pbix attached) - Measure as filter

[wildmight2017]

Good afternoon,

I have a data set which assigns grades based on the Salary rank:

Grade is a measure and calculated via 

Grade = IF(HASONEVALUE('Fact Salary'[ANNUAL SALARY]), SWITCH (TRUE(), [Salary Rank]<= [# All Employees]*.33, "A",  [Salary Rank]<= [# All Employees]*.66, "B", [Salary Rank]<= [# All Employees], "C" ))

I'd like to achieve the following:

A) Drop Grade column with # Employees column and get Count by Grade
for the filter above it would be:

Grade	# Employees
A	3
B	3
C	4

B) Use Grade in filter (can't do it now since it's a measure) 

Bining and Banding Pattern does not work here as Rank and Grade are dynamic and depend on # Employees, etc.  The real use case is more complex and could include weighted Rank by categories , departments , etc. 

Link to PBIX with mode and data  - https://www.dropbox.com/s/ib33otil2o7ib2b/sample.pbix?dl=0

Thank you! 

[Zubair_Muhammad · Accepted solution]

Hi @wildmight2017

Try this

Create a small table from the HOME TAB

Now use this MEASURE in newly created table (Table1)

Measure =
CALCULATE (
    COUNT ( 'Employee Dim'[Name] ),
    FILTER (
        ALL ( 'Employee Dim'[Name] ),
        [Grade] = SELECTEDVALUE ( Table1[Grade] )
    )
)

[Zubair_Muhammad · Accepted solution]

Hi @wildmight2017

Try this

Create a small table from the HOME TAB

Now use this MEASURE in newly created table (Table1)

Measure =
CALCULATE (
    COUNT ( 'Employee Dim'[Name] ),
    FILTER (
        ALL ( 'Employee Dim'[Name] ),
        [Grade] = SELECTEDVALUE ( Table1[Grade] )
    )
)

[Zubair_Muhammad · Accepted solution]

@wildmight2017

Please see attached file

[wildmight2017]

Thank you Muhammad for taking your time. 

It works! I wonder how well it would scale with officec / etc. , but i will try

I found another solution on the forum, create a summarized table, and re-create grade as a column with If / Switch statement.