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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Group By Count / Total Count

Hi All,

I am pretty new to Power Bi. Need help. 

I am trying to do a count of customers by Cars. And then Group them by NumberOfCount.

Example, 1st find the number (Count) of cars by Customers.

                2nd, Group them by Count.

If A has 3 cars then Group 1,

   B has 4 cars then Group 2.

               

              3. How many  Customers in Group 1, Group 2, etc.        

 

It can be achievable in SQL by using CTE Or a Derived table but I have no clue in DAX.

If anyone can shed some light I would be truly grateful.

 

       

 

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You can create columns to meet your demand.

 

Count_cars = CALCULATE(COUNT(Table1[Cars]),FILTER(ALLSELECTED(Table1),Table1[Customer]=EARLIER(Table1[Customer])))

 

Group = RANKX(ALL(Table1),Table1[Count_cars],,ASC,Dense)

 

Count Customers in Group = CALCULATE(DISTINCTCOUNT(Table1[Customer]),FILTER(ALLSELECTED(Table1),Table1[Group]=EARLIER(Table1[Group])))

 

8.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXF2nD6fvj9Og058x2YlnxgBRz3lB6a1Q4vjuhWdtV3mmQ?e=81RJX7

 

Best Regards,

Amy

 

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

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

I solved a similar problem here - https://community.powerbi.com/t5/Desktop/Customer-Frequency-Bins-DAX/m-p/670525#M322656.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You can create columns to meet your demand.

 

Count_cars = CALCULATE(COUNT(Table1[Cars]),FILTER(ALLSELECTED(Table1),Table1[Customer]=EARLIER(Table1[Customer])))

 

Group = RANKX(ALL(Table1),Table1[Count_cars],,ASC,Dense)

 

Count Customers in Group = CALCULATE(DISTINCTCOUNT(Table1[Customer]),FILTER(ALLSELECTED(Table1),Table1[Group]=EARLIER(Table1[Group])))

 

8.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXF2nD6fvj9Og058x2YlnxgBRz3lB6a1Q4vjuhWdtV3mmQ?e=81RJX7

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks Amy. I am trying to apply the same logic on my desktop. Hope I'll manage to achieve the same results.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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