topic Re: How to group by summed amounts? in DAX Commands and Tips

How to group by summed amounts?

Anonymous — Fri, 16 Sep 2022 22:56:14 GMT

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.

Re: How to group by summed amounts?

Jihwan_Kim — Sat, 17 Sep 2022 03:14:04 GMT

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

Re: How to group by summed amounts?

Anonymous — Wed, 21 Sep 2022 21:24:34 GMT

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.

Re: How to group by summed amounts?

Anonymous — Wed, 21 Sep 2022 22:09:55 GMT

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