Count occurence of text in column and then Sum the aggregated results.

[markpendlebury]

Hello everyone, hope someone can help. I'm pretty new to Power BI and Dax so I'm just getting my head around the common data modelling concepts. I've created a few calculated columns and measures etc.

However, I'm wondering the best way to acheive the following.....

I have a datasource (guarantee table) that pulls an email address column (linked from another table on a contact id). All is fine.

There can be multiple occurances of the same email address in the table.

For example

a@xyz.com

b@xyz.com

b@xyz.com

c@xyz.com

c@xyz.com

d@xyz.com

d@xyz.com

I'd like to count the number of occurances of an email address and then further sum the number of times of each occurance.

So the end result would be for example... (the table has 100,000 entries)

A single email address occurs 6532.

2 email addresses occurs 2653.

3 email addresses occurs 1625

etc

etc

etc

Ultimately this will tell me how many people (and the sum) register multiple items.

I hope that makes sense?

Thanks in advance.

[Sean · Accepted solution]

@markpendlebury

On the Modeling tab - click New Table and type

Summary Table =
SUMMARIZE (
    'Table', // this is your current table name
    'Table'[Email],
    "Number of Times Registered", COUNTROWS ( 'Table' )
)

Then create a MEASURE

Measure =
CALCULATE (
    COUNTROWS ( 'Summary Table' ),
    ALLEXCEPT ( 'Summary Table', 'Summary Table'[Number of Times Registered] )
)

Then create a table visualization

Place Number of Times Registered and click and select Do Not Summarize

Then add the Measure and I think this is the result you expect!

Good Luck!

EDIT: @Vvelarde  just wondering if you have another suggestion for this?

[Sean · Accepted solution]

@markpendlebury

On the Modeling tab - click New Table and type

Summary Table =
SUMMARIZE (
    'Table', // this is your current table name
    'Table'[Email],
    "Number of Times Registered", COUNTROWS ( 'Table' )
)

Then create a MEASURE

Measure =
CALCULATE (
    COUNTROWS ( 'Summary Table' ),
    ALLEXCEPT ( 'Summary Table', 'Summary Table'[Number of Times Registered] )
)

Then create a table visualization

Place Number of Times Registered and click and select Do Not Summarize

Then add the Measure and I think this is the result you expect!

Good Luck!

EDIT: @Vvelarde  just wondering if you have another suggestion for this?

[markpendlebury]

Thanks for the great reply. This worked perfectly and once I realised I had to visualise the data as a table before turning that into a graph it was plain sailing.

As a follow up question (that I don't expect an answer to but it may be interesting) how could I segment the summary data by date?

The original data has a a date column and it would be nice to be able to filter the graph with a slicer to restrict the values that are counted. 

Can you apply a date slicer to the results of a summary table to restrict the output?

Thanks again.

[Vvelarde · Accepted solution]

@markpendlebury

@Sean i have a alternative way:

Create a calculated column in the table:

NumberofTimes =
VAR Email = Table2[EMAIL]
RETURN
    COUNTROWS ( FILTER ( ALL ( Table2 ), Table2[EMAIL] = Email ) )

Next Step is create a Table Visual with NumberofTimes column and Distinct Count of Email Column

Note: I add more rows to table to tested.

[Sean]

@markpendlebury  Now you have to 2 solutions to choose from!

Good Luck!