How this DAX works

[TomsNg]

Hi All,

I am given a task to categorize item after the amount is summed. I got a solution provided by one of super users 'Jihwan_Kim' from this community. However, I tried but I can't realize how the DAX works. Can someone help to explain this DAX. 

*** This is the DAX ***

Sum by Category =

SUMX (

    FILTER (

        VALUES ( Data[Id] ),

        COUNTROWS (

            FILTER (

                Categories,

                CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]

                    && CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]

            )

        ) > 0

    ),

    CALCULATE ( SUM ( Data[Amount] ) )

)

this is [data] table

this is [categories] table

this is the original post

https://community.fabric.microsoft.com/t5/Desktop/Sum-of-amount-by-id-and-categorize-after-summing/m...

Many thanks

Toms

[v-yiruan-msft · Accepted solution]

Hi  @TomsNg ,

You can understand the meaning of the following dax formula in two parts:

1. It filter the Ids which the sum of Amount is between Categories[Min] and Categories[Max]

  FILTER (
        VALUES ( Data[Id] ),
        COUNTROWS (
            FILTER (
                Categories,
                CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]
                    && CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]
            )
        ) > 0
    )

2. This is calculating the sum of the Amount for each Id that meets the conditions specified in the filter. The SUMX function is an iterator that calculates the sum row by row.

SUMX (
XXX,
 CALCULATE ( SUM ( Data[Amount] ) )
)

You can refer the following official documentations to get understanding of the involved functions:

FILTER function (DAX) - DAX | Microsoft Learn

VALUES function (DAX) - DAX | Microsoft Learn

COUNTX function (DAX) - DAX | Microsoft Learn

SUMX function (DAX) - DAX | Microsoft Learn

In addition, you can create two measures as below to achieve the same requirement. You can find the details in the attachment.

Measure = 
VAR _tab =
    SUMMARIZE ( 'Data', 'Data'[Id], "@amount", SUM ( 'Data'[Amount] ) )
RETURN
    SUMX (
        FILTER (
            _tab,
            [@amount] >= SELECTEDVALUE ( 'Categories'[Min] )
                && [@amount] < SELECTEDVALUE ( 'Categories'[Max] )
        ),
        [@amount]
    )

Sum by category = SUMX(VALUES('Categories'[Category]),[Measure])

Best Regards