The number of clients who received the number of campaigns

[H0nzinn]

Hi all,

I have one data source in power bi (Desktop September 2022) which contains sent marketing campaigns to individual clients. A client can be during a time in multiple marketing campaigns. (1 row = client id, campaign id, date sent, and more).

I need to know exactly that number of clients received some number of campaigns. For example, the number of clients 100 received 1 campaign. At the same time, I don't want to create a calculated new table in power bi, because I need to keep the possibility of filters in the current data source (like a date when was sent campaigns, type of campaigns, and so on).

So result with new measures and maybe already existing columns in data source should be like that:

NoOfSentCampaigns	NoOfClients
1	100
2	120
3	400
...	...

Next I will also put this result probably to some graph.
Guys, if you have any idea how to,.. i will really appreciate that.

Thanks 😉

[Anonymous · Accepted solution]

Hi @H0nzinn ,

First create a table containing all the [NoOfSentCampaigns] values.

Table 2 = GENERATESERIES(1,DISTINCTCOUNT('Table'[client id]),1)

then please create a measure.

NoOfClients = 
SUMX (
    SUMMARIZE (
        'Table 2',
        'Table 2'[Value],
        "@count",
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        'Table',
                        'Table'[client id],
                        "count", COUNT ( 'Table'[campaign id] )
                    ),
                    [count] = SELECTEDVALUE ( 'Table 2'[Value] )
                )
            )
    ),
    [@count]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum