Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
stream| | |average events |
a | 1.97 |
b | 2.02 |
c | 2.05 |
Solved! Go to 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.
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.
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?
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.
@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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe 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?
Hey @OPS-MLTSD ,
please provide your expected result based on the sample data you provided.
Regards,
Tom
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
91 | |
82 | |
69 |
User | Count |
---|---|
159 | |
125 | |
116 | |
111 | |
95 |