Trouble with counting and grouping for one table

[MiaRose]

Hi

I have one table of data that has a row per user who has received an item. A user can receive multiple items.

What I'm trying to do is have counts of the number of items each user can have, so from 1 to 60, say, and then how many users have only one item, or 25 items.

So something like:

• Item Count | Users
• 1 | 50
• 2 | 25
• 3 | 10

Etc. 

I'm having trouble forumatling the calculated columns / measures to achieve this.

Any help would be appreciated. 

[v-yulgu-msft · Accepted solution]

Hi @MiaRose,

Not sure what is your desired output, here are two ways for your reference.

Suppose your table structure is like below:

1.  Create two measures.

Count measure =
CALCULATE (
    DISTINCTCOUNT ( 'Item-User'[Item] ),
    ALLEXCEPT ( 'Item-User', 'Item-User'[Users] )
)

Have 1 item =
CALCULATE (
    COUNT ( 'Item-User'[Users] ),
    FILTER ( ALL ( 'Item-User' ), [Count measure] = 1 )
)

Add corresponding fields into table visual.

2. Create a calculated column in source table.

Count = CALCULATE(COUNT('Item-User'[Item]),ALLEXCEPT('Item-User','Item-User'[Users]))

Create an extra table to lists all available count item values (from 1 to 60), like this:

Then, create a one to many relationship between these two tables.

Use a table visual to display data records.

Best regards,
Yuliana Gu

[Ashish_Mathur]

Hi,

Show the data and the expected result.

[MiaRose]

The data is highly sensitive, so showing the actual data isn't a possibility. I'll see if I have time to dummy up fake data that may mimic it.