Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rafay_anis
Regular Visitor

Counting specific values in columns and presenting them as rows

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 IDDepartmentReports toManager profileEmployee profileHR profile
EMP 1FinanceAhmedGoodGoodGood
EMP 2FinanceJoeBadAverageBad
EMP 3SalesCarlGoodAverageGood
EMP 4SalesCarlBadAverageAverage

 

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

ProfileManager profileEmployee profileHR profile
Good212
Average031
Bad201

 

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!

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @Rafay_anis ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1597134121490.png

 

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.

View solution in original post

4 REPLIES 4
v-lionel-msft
Community Support
Community Support

Hi @Rafay_anis ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1597134121490.png

 

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.

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

 

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

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.