Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
Tried a few solutions from the boards and none of them seem to be working for me.
I have the following sales query, it's broken down in rows by product_id so there are many reoccurring values of order_name and customer_id :
I am trying to create a calculated column for the number of orders per customer. Currently I have a measure which works correctly and returns the right values:
Total Orders = DISTINCTCOUNT('Sales Fact Table'[order_id])
However I need this to be a column as I want to use 'order count' as a slicer.
I have tried this:
Order Count = CALCULATE ( DISTINCTCOUNT ( 'Sales Fact Table'[OrderID] ), FILTER ( ALL ( 'Sales Fact Table' ), [customer_id] = MAX ( 'Sales Fact Table'[customer_id] ) ) )
This does return values, however they are incorrect.
Anyone know what the correct formula should be?
Thanks!
Solved! Go to Solution.
When is Use the formula I proposed above I get the following results (which I expect)
Do you expect something else
Try with following
Order Count = CALCULATE ( DISTINCTCOUNT ( 'Sales Fact Table'[OrderID] ), ALLEXCEPT ( 'Sales Fact Table', 'Sales Fact Table'[customer_id] ) )
Hey @Zubair_Muhammad,
Thanks so much for your reply, it helps a lot!
Tried this out but didn't work. Values were 10 or 20 times expected.
I also tried swapping around order_id and customer_id in your formula like this:
Customer Product Count = CALCULATE ( DISTINCTCOUNT ( 'Sales Fact Table'[customer_id] ), ALLEXCEPT ( 'Sales Fact Table', 'Sales Fact Table'[order_id] ) )
This was actually closer and returned the total number or products per customer. I.e returned the distinct values for product_id
I.e. Customer A purchased 49 products. However it did not return the number of orders which was 14
This is a useful number to have as well, but not quite what I was after. I feel like the solution is close but not quite there.
Any other ideas?
Hi
Could you copy paste some data with expected results.
If data is in a format (copiable) like following it becomes easy to find a solution
InvoiceDate | Product | Price | Expecetd Last Price |
Sunday, October 1, 2017 | Product A | 15 | 4 |
Wednesday, November 1, 2017 | Product B | 10 | 5 |
Thursday, March 1, 2018 | Product A | 8 | 4 |
Friday, June 1, 2018 | Product B | 5 | 5 |
Friday, June 1, 2018 | Product A | 4 | 4 |
Hey @Zubair_Muhammad
Here is an exceprt of data for two customers. Thanks for your help 🙂
purchase_date | product_id | order_id | customer_id |
24/05/2014 | 100 | 6970030474 | 6911468682 |
24/05/2014 | 200 | 6970029258 | 6911468682 |
24/05/2014 | 1111 | 6970028426 | 6911468682 |
24/05/2014 | 800 | 6970030090 | 6911468682 |
24/05/2014 | 100 | 6970029578 | 6911468682 |
24/05/2014 | 2000 | 6970028810 | 6911468682 |
27/05/2014 | 1100 | 6970098378 | 6911468682 |
1/06/2014 | 400 | 6970186122 | 6911468682 |
1/06/2014 | 900 | 6970186506 | 6911468682 |
1/06/2014 | 100 | 6970186954 | 6911468682 |
5/09/2013 | 3000 | 6966721034 | 6904791178 |
5/09/2013 | 2000 | 6966721034 | 6904791178 |
5/09/2013 | 1000 | 6966721034 | 6904791178 |
7/07/2014 | 900 | 6970956234 | 6904791178 |
8/07/2014 | 800 | 6970970506 | 6904791178 |
10/07/2014 | 1100 | 6970999754 | 6904791178 |
11/07/2014 | 400 | 6971025482 | 6904791178 |
29/01/2015 | 300 | 6980551690 | 6904791178 |
29/01/2015 | 100 | 6980551114 | 6904791178 |
29/01/2015 | 200 | 6980551690 | 6904791178 |
29/01/2015 | 200 | 6980551114 | 6904791178 |
29/01/2015 | 200 | 6980551114 | 6904791178 |
29/01/2015 | 50061 | 6980551114 | 6904791178 |
29/01/2015 | 1300 | 6980551690 | 6904791178 |
11/02/2015 | 20000 | 6980978890 | 6904791178 |
11/02/2015 | 6174 | 6980978890 | 6904791178 |
11/02/2015 | 8946 | 6980978890 | 6904791178 |
When is Use the formula I proposed above I get the following results (which I expect)
Do you expect something else
Have similar scenario I am trying to figure out. What if I wanted to take the count of orders where the Product ID was lets say less than 1000 in the example provided. This would result in customer 6911458582 having 7 (instead of 10) in the example.
Appreciate any help in what additional logic should be applied.
I'm sorry your solution does work indeed!
I got confused, I had to turn it to 'Do Not Summarize Data' in my table!
Thanks so much for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |