Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Actually upon further examination, the solution isn't using the additional tables, but it still doesn't work properly when any filter is applied.
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.
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
)
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
75 | |
58 | |
47 | |
16 | |
12 |