powerbidaxdata-analysispowerbi-desktopmeasure

PowerBI - Group by Count with Detail?


I am trying to get a table put together, and shows the # of Policies, and then the # of Users that makes up that policy count.

My table, basically, is as follows:

User    Policy #
A       67728
A       86440
A       38497
B       11959
C       53988
C       36813
C       38102
B       3030
Z       90934
X       50008
Y       97409

So, I can see that User A has 3 policies. User B has 2 policies, User C has 3, and then users X, Y, and Z each have one.

How can I create a table that shows the # of Policies along the left, and then the # of users that make up that group?

# Policies  Total Users
1           3
2           1
3           2

This way, I can see that there are 3 users each having 1 policy. There's only 1 user with two policies, etc. It would be a bonus if I could then expand on the "Total Users" and it shows me more detail, e.g. the users in that group.

Note that the data may have users with more than 3, so I can't "hard code" the groups into 1, 2, 3, etc. It should be "dynamic".

I am quite good in Excel but am relatively new to PowerBI/DAX expressions so I'm not sure even where/how to begin.


Solution

  • This is an easy way to do static segmentation. If you want dynamic segmentation, then see here.

    enter image description here

    Policy Count = 
    VAR user = 'Table'[User]
    RETURN
    CALCULATE(COUNTROWS('Table'), 'Table'[User] = user, REMOVEFILTERS()) 
    

    Create a measure:

    Measure = DISTINCTCOUNT('Table'[User])
    

    Create table 1:

    enter image description here

    Create table 2:

    enter image description here

    Table 1 will now filter table 2 when you click on a bucket.