Counting specific values in columns and presenting them as rows

[Rafay_anis]

Hi everyone!

This seems to a very simple requirement but I seem to be stuck at it forever!

I have a data source table (Employee list) which has data in the following layout. The Manager profile, employee profile and HR profile are all custom columns. Basically these are profiles generated based on a result of a questionniare and ranking criteria from 1 - 3, with 1 being good and 3 being bad.

EMP ID	Department	Reports to	Manager profile	Employee profile	HR profile
EMP 1	Finance	Ahmed	Good	Good	Good
EMP 2	Finance	Joe	Bad	Average	Bad
EMP 3	Sales	Carl	Good	Average	Good
EMP 4	Sales	Carl	Bad	Average	Average

I need to present the data as follows, showing the count of each profile value:

Profile	Manager profile	Employee profile	HR profile
Good	2	1	2
Average	0	3	1
Bad	2	0	1

I made some progress by searching the community and did the following:

• Created a table (Profiles) and added the profiles "Good", "Average" and "Bad".

Profile

Good

Average

Bad

• Added three custom columns and used the following query for each column:

Manager = CALCULATE(
    COUNTROWS('Employee List'),
    FILTER(
        'Employee List',
        'Employee List'[Profile manager] = Profiles[Profile]
    )
)

Employee = CALCULATE(
    COUNTROWS('Employee List'),
    FILTER(
        'Employee List',
        'Employee List'[Profile employee] = Profiles[Profile]
    )
)

HR = CALCULATE(
    COUNTROWS('Employee List'),
    FILTER(
        'Employee List',
        'Employee List'[HR Profile] = Profiles[Profile]
    )
)

The above seems to be working fine when no visual filters are applied. However, the report I am creating has two filters i.e. Department and Reports to. As soon as I apply the filters, the count in the custom columns still show the count for all values.

I read on other post that we can try to Pivot/Unpivot the data. But when I go to the query editor, as these are custom columns in the Employee list table, they do not appear.

Any help on this will be highly appreciated!

[v-lionel-msft · Accepted solution]

Hi @Rafay_anis ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rafay_anis]

@v-lionel-msftWorked like a charm! Thanks a lot.

@mahoneypat& @Ashish_Mathur thank you guys for the input!

[Ashish_Mathur]

Hi,

Share your original dataset (the one in which you do not have Manager profile, employee profile and HR profile columns).  Also, share the logic that you have used in your calculated column formula for generating those 3 columns.

[mahoneypat]

Here is one way to get your desired result.

1. Start with your original table in the query editor (you will not be using your calculated columns)

2. Ctrl Click to select your first three columns

3. Right click and choose unpivot other columns

4. This will result in 5 columns - your original three plus Attribute and Value

5. Close and load that new table

6. Make a matrix visual with the Attribute column in the columns, the Value column in the rows, and the Value column in the values area.  Pull down and choose count aggregation for values (if it doesn't default to that).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat