Forum Discussion
GROUP BY expression as a measure
Hello smart people!
I have a data like this:
And need to build a chart which will show how many books on average each player had daily.
So I am trying to get (what should be) a fairly simple calculation. Something like
The chart I'm looking for should be like this
but only with the avg per user
PS
Is there a way to attach my sample workbook?
Hi Anonymous ,
Please follow these steps:
(1) Create a new measure
AVG = DISTINCTCOUNT('Table'[book_id])/DISTINCTCOUNT('Table'[user_id])(2)Final output
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
v-jialluo-msftCommunity Support
Hi Anonymous ,
Please follow these steps:
(1) Create new measures
BOOK BY GROUP = CALCULATE(DISTINCTCOUNT('Table'[book_ id]),FILTER(ALL('Table'),'Table'[group] = MAX('Table'[group]))) BOOK BY USER = CALCULATE(DISTINCTCOUNT('Table'[book_ id]),FILTER(ALL('Table'),'Table'[user_ id] = MAX('Table'[user_ id])))(2)Final output
If that's not what you need, please give sample files and expected output.
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- henkvankester
Helper I
or you should use allexcept, something like:
CALCULATE(DISTINCTCOUNT(bookid), ALLEXCEPT(table1, table1[group]))
- v-jialluo-msft
Hi Anonymous ,
Please follow these steps:
(1) Create a new measure
AVG = DISTINCTCOUNT('Table'[book_id])/DISTINCTCOUNT('Table'[user_id])(2)Final output
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- aj1973
Community Champion
Hi Anonymous
You can use online services like Onedrive or Google Drive....
- Ashish_Mathur
Super User
Hi,
In a simple Table format, please show the expected result.
Sorry, none of the solutions seems working for me. Here is a detailed screenshot of what I'd like to get
So, on 10/02/2022 on average each user had 1.5 red book and 1 blue book. And this average across users is what I want to put on the chart.
So in SQL terms I need (1) to group by date, user and colour first, get the aggregation (count) and (2) group by date and colour and take the avg of the aggregation in the step (1).
Sample workbook with the wrong chart is attached. Let me know if the link doesn't work.
Thank you!
- Ashish_Mathur
