find average number of events attended per Client ID

[OPS-MLTSD]

Hello, 

I have a dataset where I have 20,400 Client Ids who attend different events (there are 88 events in total). Each event falls under one of 4 category types: Housing, Employment, Finance, and Creative. I am trying to create a new column that shows the average number of events attended by each client in the 3 different streams (a,b,c). Here is what my dataset looks like:

client id	event	type	Client stream
123456	housing help	housing	a
123456	employment help	employment	a
1378910	resume writing	employment	c
138801	job search	employment	b
138801	housing help	housing	b
134461	career advice	employment	c
1378111	mortgage help	finance	a
1378111	career advice	employment	a
1378111	resume writing	employment	a
1378111	budget skills	finance	a
134882	writers block	creative	b
134822	budget skills	finance	b
134566	employment help	employment	c
134672	resume writing	employment	b
134672	job search	employment	b
134672	loan service	finance	b

if someone could please let me know how i can create the new column to calculate average events atteded by each clients in the 3 different streams, that would be great. Thank you

EDIT: I tried the dax below but I don't think it worked: 

Count of event average per STREAM =

CALCULATE (

AVERAGEX (

VALUES ( 'mytable'[Client id] ),

CALCULATE (

VAR eventitems =

SUMMARIZE ( 'mytable','mytable'[event] )

RETURN

SUMX ( eventitems, 1 ))))

here is the result I am getting

stream|	|average events
a	1.97
b	2.02
c	2.05

[v-chenwuz-msft]

Hi OPS-MLTSD,

First Create a measure

AVG =

DIVIDE(

    COUNTROWS(FILTER(ALL('table'),'table'[Client stream]=MAX('table'[Client stream]))),

    CALCULATE(DISTINCTCOUNT('table'[client id]),FILTER(ALL('table'),'table'[Client stream]=MAX('table'[Client stream])))

)

Then Place Client stream,event field and AVG measure.in the table in order.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OPS-MLTSD]

i tried the method you posted but the numbers are showing up weird. 

a note: the column "Client Stream" is from another table called "Stream" I have created a relationship between the Stream table and "mytable" using Client ID; the relationship is one to many. There are distinct client IDs in the Stream table and many client IDs in mytable since one client can attend multiple events. 

Is there a way to create a column or a measure that counts the average number of distinct events attended for all clients? 

[v-chenwuz-msft · Accepted solution]

Hi, I have created these two tables based on your description.

Stream table

Client Stream	client IDs
a	123456
a	1378111
b	138801
b	134882
b	134822
b	134672
c	1378910
c	134461
c	134566

Mytable

client IDs	events	type
123456	housing help	housing
123456	employment help	employment
1378111	mortgage help	finance
1378111	career advice	employment
1378111	resume writing	employment
1378111	budget skills	finance
138801	job search	employment
138801	housing help	housing
134882	writers block	creative
134822	budget skills	finance
134672	resume writing	employment
134672	job search	employment
134672	loan service	finance
1378910	resume writing	employment
134461	career advice	employment
134566	employment help	employment

and their relationship

Create a column in Stream table

Distinctevents = CALCULATE(DISTINCTCOUNT(mytable[events]),FILTER('mytable','mytable'[client IDs]='Stream'[client IDs]))

Then drag it to table visual and Set it to the average via the drop down menu.

Result：

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OPS-MLTSD]

@v-chenwuz-msft  Hello, I tried the dax you created above and I tried to add it to mytable and it was not recognized there. 

This is the part that gave me an error: FILTER('mytable','mytable'[client IDs]='Stream'[client IDs]))

[v-chenwuz-msft]

hi @OPS-MLTSD ，

You cannot add to a mytable. If you don't want to add to the stream table, you can make a copy of the stream table.

Here is my pbix file that you can refer to.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OPS-MLTSD]

I am not able to open your file since I am using an older version of Power BI (oct 2020) due to company policy, I cannot upgrade to the newer version, would it be possible for you to upload the screenshots? Thank you

[v-chenwuz-msft]

Here are screenshots. mytable and stream table have been mentioned before.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Is this what you mean?  Here is a measure that shows one way to do this.  Replace "Clients" with your actual table name.

Client Stream Count =
CALCULATE (
    COUNTROWS ( Clients ),
    ALLEXCEPT ( Clients, Clients[client id], Clients[Client stream] )
)

Pat

[OPS-MLTSD]

thanks Pat, I am looking for the average number of events attended per client in each stream. Is there a way to create a measure for the average number of events attended by clients in the 3 streams?

[TomMartens]

Hey @OPS-MLTSD ,

please provide your expected result based on the sample data you provided.

Regards,

Tom

[OPS-MLTSD]

hello, 

 could you explain what you mean by expected result? The expected result is the new column that I am trying to create that should contain average number of events attended per client in each stream. It could either be a measure or a new column, whichever is easier to create. Although I think a column would be better so that it can contains row level data