How to group by summed amounts?

[Anonymous]

I'm trying to figure out how take the total sales amount for filtered list of customers, catagorize them (e.g., Large, Medium, Small) and provide a count for those catagories.

For instance, if I want Large >= $100K, Med $25K to $100K , Small under $25K and my table is:

Customer      Product     Amount

================================

Customer A     ABC       $80,000

Customer A     XYZ       $40,000

Customer B     ABC       $20,000

Customer B     XYZ       $60,000

Customer c     XYZ        $7,000

I would like to be able to produce:

Category      Count

==============

Large             1

Medium        1

Small             1

If I were using Tableau, I would use a CASE statement and a LEVEL OF DETAIL calc, but I can't figure out how to do this with DAX. Also, I'm using Direct Query, so functions like ALLCOLUMNS won't work.

[Anonymous]

Actually upon further examination, the solution isn't using the additional tables, but it still doesn't work properly when any filter is applied.

[Anonymous]

This solution breaks the data query out into separate joined datasets. My data is structured like shown above, in a single flat file. As such, this solutions doesn't work as it counts multiple rows for each customer and doesn't aggregate them. I need to keep my original query as to much stuff is already built on it, but I guess I could build new queries to use this solution. I just wish I could do it as is, with different DAX statement.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating measures to solve.

Customers count by category: =
CALCULATE (
    COUNTROWS ( Customer ),
    FILTER (
        Customer,
        COUNTROWS (
            FILTER (
                Category,
                [Sales amount:] >= Category[Min]
                    && [Sales amount:] < Category[Max]
            )
        ) >= 1
    )
)