Count the frequency of a record by frequency

[cwharris15]

Hi Community! 

I have a data set where I'm trying to identify the frequency an order number is repeated by the number of times it is repeated. For example in the set below I have 4 orders. Each item that is in the order has a separate record. Therefore there are 4 orders that consist of (7) different items.

Order Number	Item
123	ABC
123	DEF
456	GHI
789	JKL
789	MNO
789	PQR
101	STU

I am trying to visualize the number of items that are in a box by the number of items to return something like below. Where I can see that only 1 item was need for 2 orders (frequency), 2 items were needed for 1 order, and 3 items were needed for 1 order. 

Number of items	Frequency
1	2
2	1
3	1

[rajendraongole1 · Accepted solution]

Hi @cwharris15 - create a new calculated table that counts the number of items per order as below:

OrderItemCount =

ADDCOLUMNS(

    SUMMARIZE(

        'Frequ',

        'Frequ'[Order Number],

        "ItemCount", COUNT('Frequ'[Item])

    ),

    "Frequency", COUNTROWS(FILTER('Frequ', [Order Number] = EARLIER('Frequ'[Order Number])))

)

Create one more table using calculated table to summarizes the number of orders by the number of items.

ItemFrequency =

SUMMARIZE(

    OrderItemCount,

    OrderItemCount[ItemCount],

    "Frequency", COUNT(OrderItemCount[Order Number])

)

Now take one table visual in report view add the fields Item count and Frequency

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!