Forum Discussion
Frequency distribution from raw data
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!
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.
2 Replies
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.