Forum Discussion

pieterhkruger's avatar
pieterhkruger
Frequent Visitor
7 years ago
Solved

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 particul...
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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]

     

    Regards,

    Owen

pieterhkruger's avatar
pieterhkruger
Frequent Visitor
7 years ago

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

 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
7 years ago

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]

 

Regards,

Owen

  • pieterhkruger's avatar
    pieterhkruger
    Frequent Visitor
    7 years ago

    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).