Forum Discussion

warabimochi's avatar
warabimochi
Regular Visitor
1 year ago
Solved

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 0...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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
            )
        )
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.