Forum Discussion
Distribution of counts
Hi,
I would like to calculate the distriution of the number of occurences of a field. More specifically, I want to see the distribution of the number of applications customers have in a particular time period.
For example, I can easily for the first column (customer number) determine how many applications they made:
However, I want to be able to create a graph / summary table that would tell me:
| Number of applications | Number of customers (having this number of applications) |
| 1 | 11 |
| 2 | 1 |
How would I go about doing so with DAX (and without creating a table that would not filter according to filters on a page)?
To make this work, I believe you'll need to create a disconnected dimension table containing all possible "count" values.
Then create a Frequency measure to count the number of CUSTOMER_NUMBERs with a given count.
See for example this pbix using your sample data
I created a table called 'Count' and a measure Frequency
Count = VAR MaxCount = MAXX ( VALUES ( YourTable[CUSTOMER_NUMBER] ), CALCULATE ( COUNTROWS ( YourTable ) ) ) RETURN SELECTCOLUMNS ( GENERATESERIES ( 0, MaxCount ), "Count", [Value] )Frequency = SUMX ( 'Count', COUNTROWS ( FILTER ( VALUES ( YourTable[CUSTOMER_NUMBER] ), CALCULATE ( COUNTROWS ( YourTable ) ) = 'Count'[Count] ) ) )The Frequency measure uses SUMX to iterate over the 'Count' table so it can aggregate over multiple Count values.
You can then create visuals showing Frequency filtered by 'Count'[Count]
Regards,
Owen
6 Replies
1. Create a measure Distribution = COUNT(yourtablename[Customer])
2. Now create a table visual with
YourTable[Count] & Distribution as values.
For YourTable[Count] value set to do not summarize.
CheenuSing
Hi,
Thanks for the response.
I don't exactly understand how to do the second step. Do I have to use the SUMMARIZE statement? But in that case I cannot use a calculated field as the second parameter. Could you perhaps provide an example?
Thanks
