Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
warabimochi
Regular Visitor

Sum values for each category

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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1732077298217.png

 

 

Jihwan_Kim_0-1732077275556.png

 

 

Order count: = 
SUMX (
    VALUES ( Fruit[Fruit] ),
    COUNTROWS (
        CALCULATETABLE (
            FILTER (
                SUMMARIZE ( Data, Data[Customer  ID], Data[Order Date], Data[quantity] ),
                Data[quantity] > 0
            )
        )
    )
)

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @warabimochi ,

 

According to your statement, I think your table looks like as below.

vrzhoumsft_1-1732154491613.png

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.

vrzhoumsft_2-1732157245961.png

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

HI @warabimochi ,

 

why apple is counted as 1, not 0 here?

FreemanZ_0-1732085982426.png

 

Kedar_Pande
Super User
Super User

@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
Super User
Super User

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

Orange3
Banana4
Apple4

 

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

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 - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1732077298217.png

 

 

Jihwan_Kim_0-1732077275556.png

 

 

Order count: = 
SUMX (
    VALUES ( Fruit[Fruit] ),
    COUNTROWS (
        CALCULATETABLE (
            FILTER (
                SUMMARIZE ( Data, Data[Customer  ID], Data[Order Date], Data[quantity] ),
                Data[quantity] > 0
            )
        )
    )
)

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
shafiz_p
Super User
Super User

Hi @warabimochi  Try this to create a measure :

 

TotalOrders = 
SUMX(
    VALUES('Table'[Fruit]),
    CALCULATE(SUM('Table'[OrderCount]))
)

 

 

Output:

shafiz_p_0-1732076715088.png

 

 

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

 

Best Regards,
Shahariar Hafiz

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.