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

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago
All you need is create the measure as at step 1.

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

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