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

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

Accepted Solutions
v-xicai Senior Member
Senior Member

Re: Group By Count / Total Count

Hi @RizzLearn ,

 

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/EXF2nD6fvj9Og058x2...

 

Best Regards,

Amy

 

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

3 REPLIES 3
v-xicai Senior Member
Senior Member

Re: Group By Count / Total Count

Hi @RizzLearn ,

 

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/EXF2nD6fvj9Og058x2...

 

Best Regards,

Amy

 

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

Highlighted
RizzLearn Frequent Visitor
Frequent Visitor

Re: Group By Count / Total Count

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

Re: Group By Count / Total Count

Hi,

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

Hope this helps.