Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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])))
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.
Hi,
I solved a similar problem here - https://community.powerbi.com/t5/Desktop/Customer-Frequency-Bins-DAX/m-p/670525#M322656.
Hope this helps.
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])))
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |