Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
OPS-MLTSD
Post Patron
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 ideventtypeClient stream
123456housing helphousinga
123456employment helpemploymenta
1378910resume writingemploymentc
138801job searchemploymentb
138801housing helphousing b
134461career adviceemploymentc
1378111mortgage helpfinancea
1378111career adviceemploymenta
1378111resume writingemploymenta
1378111budget skillsfinancea
134882writers blockcreativeb
134822budget skillsfinanceb
134566employment helpemploymentc
134672resume writingemploymentb
134672job searchemploymentb
134672loan servicefinance 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
a1.97
b2.02
c2.05
 
 

 

1 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

vchenwuzmsft_0-1631074563165.png

 

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.

vchenwuzmsft_1-1631074563167.png

 

Result:

vchenwuzmsft_2-1631074563169.png

 

 

 

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.

View solution in original post

11 REPLIES 11
v-chenwuz-msft
Community Support
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.

vchenwuzmsft_1-1630997388050.png

 

 

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.

 

 

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

OPSMLTSD_0-1631024510784.png

 

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? 

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

vchenwuzmsft_0-1631074563165.png

 

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.

vchenwuzmsft_1-1631074563167.png

 

Result:

vchenwuzmsft_2-1631074563169.png

 

 

 

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.

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

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.

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

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

vchenwuzmsft_0-1632121297491.jpeg

 

vchenwuzmsft_1-1632121297494.jpeg

 

vchenwuzmsft_2-1632121297500.jpeg

 

 

 

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
Microsoft Employee
Microsoft Employee

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

 

mahoneypat_0-1630951251275.png

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Super User
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

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  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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