Forum Discussion
Dynamic Grade Column - aggregate by (pbix attached) - Measure as filter
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!
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] ) ) )
3 Replies
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] ) ) )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.
