cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Farol
Frequent Visitor

GROUP BY expression as a measure

Hello smart people!

I have a data like this:

Farol_0-1669257993244.png

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

_n_per_user = GROUPBY('Table', 'Table'[user_id], "total_n_books", DISTINCTCOUNT('Table'[book_id]))
The only problem is that GROUPBY returns a table, not a scalar, right? So I can't use it as a measure in my visual.
How can I get this simple calculation in DAX?
I don't what to have calculated table cause I need to be able to apply filters to my visual (like dates and colours)

The chart I'm looking for should be like this

Farol_0-1669258584772.png

but only with the avg per user

 

PS

Is there a way to attach my sample workbook?

1 ACCEPTED SOLUTION
v-jialluo-msft
Community Support
Community Support

Hi @Farol ,

 

Please follow these steps:

(1) Create a new measure

AVG = DISTINCTCOUNT('Table'[book_id])/DISTINCTCOUNT('Table'[user_id])

 

(2)Final output

vjialluomsft_0-1669341320754.png

 

 

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.

View solution in original post

9 REPLIES 9
v-jialluo-msft
Community Support
Community Support

Hi @Farol ,

 

Please follow these steps:

(1) Create a new measure

AVG = DISTINCTCOUNT('Table'[book_id])/DISTINCTCOUNT('Table'[user_id])

 

(2)Final output

vjialluomsft_0-1669341320754.png

 

 

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.

Farol
Frequent Visitor

Sorry, none of the solutions seems working for me. Here is a detailed screenshot of what I'd like to get

Farol_0-1669325155918.png

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
henkvankester
Helper I
Helper I

or you should use allexcept, something like: 

 

CALCULATE(DISTINCTCOUNT(bookid), ALLEXCEPT(table1, table1[group]))

v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1669272979610.png

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.

Ashish_Mathur
Super User
Super User

Hi,

In a simple Table format, please show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
aj1973
Community Champion
Community Champion

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.