Sum values for each category

[warabimochi]

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!

[Anonymous]

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.

[FreemanZ]

HI @warabimochi ,

why apple is counted as 1, not 0 here?

[Kedar_Pande]

@warabimochi 

Create a Measure

TotalOrdersPerFruit = 
SUMX(
'YourTable',
'YourTable'[OrderCount]
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Poojara_D12]

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.

1. Create a Measure

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.

2. Create a Calculated Column (if needed)

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.

3. Create a Summary Table (Optional)

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.

Expected Output

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

[Jihwan_Kim · Accepted 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
            )
        )
    )
)

[shafiz_p]

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