Frequency distribution from raw data

[tmv3v]

Currently say I have some sales data:

CustomerID ProductID CustomerCountry PricePaid

12345678 P1234567 US 300

12345678 P1234568 US 300

12345678 P1234569 US 300

12345670 P1234567 US 300

12345670 P1234568 US 300

12345672 P1234567 US 300

12345672 P1234568 US 300

12345671 P1234567 US 300

And I want a result table such that I want to count distinct on CustomerID and obtain the frequency distribution of the number of purchases:

NoOfPurchases NoOfCustomers

1 1

2 2

3 1

I am able to do this with SQL using temp tables then importing the temp tables into PowerBI but I am finding a way do achieve this in PowerBI directly from the raw data as all my tables are depending on the raw data table. I guess using only one data table will reduce the loading time too? Thanks!

[rajulshah · Accepted solution]

Hello @tmv3v,

Please create a calculated column as follows:

Number of Purchases = CALCULATE(COUNT(Purchases[ProductID]),FILTER(ALL(Purchases),Purchases[CustomerID]=EARLIER(Purchases[CustomerID])))

Then, plot 'Number of Purchases' and CustomerId in a table visual.

Make sure to select 'Don't summarize' for 'Number of Purchases' and 'Count' for 'CustomerID'. See below:

Hope this helps.

[rajulshah]

Hello @tmv3v,

If the solution helped you, then please mark the post as Solution so that it is easy for others in need.

Thanks. 🙂