Forum Discussion
Count of Measure
I need help in creating a measure (on top table) that would calculate the # of employees per category (from bottom table).
If possible, I would also like to be able to filter the bottom table with the employees applicable when I click on an Account-Category combination.
Thanks!
If I'm understanding correctly you can just use group category and account in a matrix to create the top table.
Add distinct employee id count into the value for the matrix. You don't need a new measure drag emp id in and change set the aggregation from the down arrow menu in the values list.
As it's based on the same data it would cross highlight/filter.
7 Replies
I need help in creating a measure (for top table) to calculate the # agents per category (bottom table).
Thanks!!
- Greg_Deckler
Community Champion
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- stretcharm
Memorable Member
If I'm understanding correctly you can just use group category and account in a matrix to create the top table.
Add distinct employee id count into the value for the matrix. You don't need a new measure drag emp id in and change set the aggregation from the down arrow menu in the values list.
As it's based on the same data it would cross highlight/filter.
Im unable to add the 'Hrs Grp Category' measure as a column to the matrix table.
- stretcharm
Is hrs group a dax measure?
Try using grouping/binning
https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning
or use a conditional column in M (Query Editor)
I created a summarized table for all entries then created the Hrs Grp category as a calculated column on that table. I then followed your steps and it worked!
The bottom table also gets filtered already when I select any Site-Category combination.
Only remaining issue I have is getting the data on the top table to show correctly on the chart. I dont have the % of Row Total available as value. Currently, it's on % of Grand Total.- stretcharm
Cool. My next step would have been to create a summary table if the row level group is not going to work.
I did mine in as a reference of the first table and grouped in M adding a conditional column for the hr group.
I created a composite key of Account + EmpID to join the two tables.
Not sure why you've not got % of row. I get % of grand, column and row options.
