cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Patron

## find average number of events attended per Client ID

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

1 ACCEPTED SOLUTION
Community Support

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.

11 REPLIES 11
Community Support

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.

Post Patron

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?

Community Support

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.

Post Patron

@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]))

Community Support

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.

Post Patron

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

Community Support

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.

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Post Patron

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?

Super User

Hey @OPS-MLTSD ,

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

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Post Patron

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors