CODEWITHSUNDEEP
powerbi
ands8810
ands8810

Reputation: 3

PowerBi measure and summarize row for report

I am new to PowerBi.

What I am trying to do is summarize an email marketing report for open, clicks with the exported data. The data looks like

|   Campaign Name  |     Email Address     | Event Type |
|:---------------: |:---------------------:| :--------: |
| Test Campaign    | [email protected]      | Open       |
| Test Campaign    | [email protected]      | Open       |
| Test Campaign    | [email protected]   | Open       |
| Test Campaign 2  | [email protected]      | Open       |
| Test Campaign 2  | [email protected]      | Open       |
| Test Campaign 2  | [email protected]  | Open       |
| Test Campaign 2  | [email protected]      | Clicked    |
| Test Campaign 2  | [email protected]      | Clicked    |

I want to calculate each event type that is unique for each email address and summarize it by the campaign name. There can be duplicate email address if someone opens email twice. And there are multiple different campaign names

I want PowerBI to be able to take the above data and summarize it as such with a measure formula :

Campaign Name Open Click
Test Campaign 2 0
Test Campaign 2 2 1

Any suggestions? I can't get it to summarize by campaign name.

Clicks = Calculate(DistinctCount('table'[column]),'table2 for event type'[event type] = "click"))

Upvotes: 0

Views: 219

Answers (1)

Jérémie L
Jérémie L

Reputation: 371

You can do it in DAX very quickly.

In PowerBI, you create two new measures:

Please note that I've used 'Table' as the table name, replace it with the actual name of your table.

'Table'[Open_Nb] =
CALCULATE(
    DISTINCTCOUNT('Table'[Email Address ]),
    'Table'[Event Type] = "Open"
)

and

'Table'[Clicked_Nb] =
CALCULATE(
    DISTINCTCOUNT('Table'[Email Address ]),
    'Table'[Event Type] = "Clicked"
)

Then you can use this table two ways. You can create a new Table visual and drag and drop :

  • Campaign Name
  • Measure Open_Nb
  • Measure Clicked_Nb

Or, directly as a new table with the expression :

ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Campaign Name ]
    ),
    "Open",
    'Table'[Open_Nb],
    "Click",
    'Table'[Clicked_Nb]
)

Upvotes: 1

Related Questions