Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pieterhkruger
Frequent Visitor

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:

Accounts.GIF

However, I want to be able to create a graph / summary table that would tell me:

Number of applicationsNumber of customers (having this number of applications)
111
21

 

How would I go about doing so with DAX (and without creating a table that would not filter according to filters on a page)?

1 ACCEPTED SOLUTION

@pieterhkruger 

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]

 

image.png

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
CheenuSing
Community Champion
Community Champion

Hi @pieterhkruger 

 

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

All you need is create the measure as at step 1.

For step 2 it is the table visual from visualisation panel.

CheenuSing
Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Sorry, maybe I understand your solution incorrectly, but let me explain with an example:

 

If I have the following data:

ACCOUNT_NUMBERCUSTOMER_NUMBER
1A
2B
3C
4D
5E
6E
7F

 

The creating a measure - COUNT([CUSTOMER_NUMBER]) and creating a visual table based on that, would give me:

CUSTOMER_NUMBERCOUNT(Customer_number)
A1
B1
C1
D1
E2
F1

 

However, what I want, requires another step, because what I now want is this:

COUNT(Customer_number)COUNT(COUNT(CUSTOMER_NUMBER))
15
21

 

@pieterhkruger 

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]

 

image.png

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you very much!

This works well, especially for showing the distribution over the whole range of possible counts - which can be beneficial.

 

(I also got it to work with a COUNT and SUMMARIZE statement, together with a bi-directional join between the 2 tables, in which case it only keeps the range of counts relevant for the selection).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.