Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, I am new to PBI and I would like to know how to derive the number of orders placed for each fruit using DAX.
Customer ID | Order Date | Fruit |
| quantity | OrderCount |
1 | 01 Jan 2021 | Orange |
| 4 | 1 |
1 | 01 Jan 2021 | Banana |
| 2 | 1 |
1 | 01 Jan 2021 | Apple |
| 0 | 1 |
1 | 19 Feb 2021 | Orange |
| 2 | 1 |
1 | 19 Feb 2023 | Banana |
| 2 | 1 |
1 | 19 Sept 2023 | Apple |
| 6 | 1 |
2 | 6 Jul 2022 | Orange |
| 3 | 1 |
2 | 9 Dec 2022 | Banana |
| 1 | 1 |
2 | 9 Dec 2022 | Apple |
| 7 | 1 |
3 | 4 Mar 2022 | Orange |
| 0 | 0 |
3 | 4 Mar 2022 | Banana |
| 0 | 0 |
3 | 4 Mar 2022 | Apple |
| 1 | 1 |
I have a conditional column for OrdersCount where if the quantity is > 0, then the OrderCount is = 1.
What I would like to achieve is to calculate the sum of Ordercount for each fruit (Order Count for Orange is 3, apple is 4 etc) .
TIA!
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Order count: =
SUMX (
VALUES ( Fruit[Fruit] ),
COUNTROWS (
CALCULATETABLE (
FILTER (
SUMMARIZE ( Data, Data[Customer ID], Data[Order Date], Data[quantity] ),
Data[quantity] > 0
)
)
)
)
Hi @warabimochi ,
According to your statement, I think your table looks like as below.
You should want to add an [OrderCount] column to mark fruits with an aggregated QUANTITY greater than 0 as 1 by Customer ID, and 0 otherwise.
You can try ocde as below to create a calculated column.
OrderCount =
VAR _SUM =CALCULATE(SUM('Table'[quantity]),ALLEXCEPT('Table','Table'[Customer ID],'Table'[Fruit]))
RETURN
IF(_SUM>0,1,0)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Create a Measure
TotalOrdersPerFruit =
SUMX(
'YourTable',
'YourTable'[OrderCount]
)
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Hi @warabimochi
To calculate the total OrderCount for each fruit in Power BI using DAX, you can create a calculated measure or a calculated column depending on how you want to aggregate the data.
If you want to dynamically calculate the total OrderCount for each fruit when visualized, you can use the following DAX formula:
Total OrderCount = SUM('Table'[OrderCount])
This measure will sum up the Or
derCount column for all rows in your table. When you create a visual (e.g., a bar chart or table), place Fruit on the axis and this measure in the values to see the totals grouped by fruit.
If you want to calculate the total OrderCount for each fruit within the table itself, you can use this formula in a calculated column:
Total OrderCount by Fruit =
CALCULATE(
SUM('Table'[OrderCount]),
ALLEXCEPT('Table', 'Table'[Fruit])
)
This will create a column where the total OrderCount for each fruit is shown for every row corresponding to that fruit.
If you'd like to create a summary table displaying only the fruit and their respective OrderCount, you can use the following DAX to create a new table:
Fruit Order Summary =
SUMMARIZE(
'Table',
'Table'[Fruit],
"Total OrderCount", SUM('Table'[OrderCount])
)
This creates a new table where each row contains a unique fruit and its total OrderCount.
With the given dataset, the output would be:
Fruit Total OrderCount
Orange | 3 |
Banana | 4 |
Apple | 4 |
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Order count: =
SUMX (
VALUES ( Fruit[Fruit] ),
COUNTROWS (
CALCULATETABLE (
FILTER (
SUMMARIZE ( Data, Data[Customer ID], Data[Order Date], Data[quantity] ),
Data[quantity] > 0
)
)
)
)
Hi @warabimochi Try this to create a measure :
TotalOrders =
SUMX(
VALUES('Table'[Fruit]),
CALCULATE(SUM('Table'[OrderCount]))
)
Output:
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |