Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
Hi @Farol ,
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.
Hi @Farol ,
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.
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!
Hi,
You may download my PBI file from here.
Hope this helps.
yep, that works. I am even confused how easy it is. need to learn DAX aggregation logic.
Hi,
If my previous reply helped, please mark that as Answer.
or you should use allexcept, something like:
CALCULATE(DISTINCTCOUNT(bookid), ALLEXCEPT(table1, table1[group]))
Hi @Farol ,
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.
Hi,
In a simple Table format, please show the expected result.
Hi @Farol
You can use online services like Onedrive or Google Drive....
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |