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 Super Contributor
Super Contributor

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 Super Contributor
Super Contributor

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.

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.