Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a matrix with a measure for Gross Profit %, and in another matrix i want to group the items in % ranges, and count the number of items in each.
To calculate the gross profit % in the first matrix, i used: Gross Profit% = CALCULATE(([Total Sales]-[Total Cost])/[Total Sales]) Total sales and total cost are measures to sum the columns in the sales order detail table.
Here is the measure i tried to create for the new matrix, but it didnt work: GP% Category = COUNTAX('sales order detail',IF([Gross Profit%] <0, "-Negative", IF([Gross Profit%]>-1 && [Gross Profit%] <11, "0-10%", IF([Gross Profit%]>10 && [Gross Profit%]<21, "11-20%", IF([Gross Profit%]>20 && [Gross Profit%]<31, "21-30%", IF([Gross Profit%]>30 && [Gross Profit%]<61, "31-60%", IF([Gross Profit%]>60 && [Gross Profit%]<81, "61-80%", IF([Gross Profit%]>80, "81% and Higher", "Null"))))))))
Visual 1 is the matrix i have built, and visual 2 is what i want to build.
Current Matrix
Add'l matrix i want to create
Solved! Go to Solution.
@Anonymous,
You'll need to add a configuration table first.
https://www.daxpatterns.com/dynamic-segmentation/
Hi @Anonymous. Did you ever figure this out? I am having to do the same thing with my data.
@Anonymous No, i didn't unfortunately. I struggled with it for awhile and then just gave up and moved on to other things.
Actually, the Below Article can give us a good reference:
https://www.burningsuit.co.uk/blog/2018/06/dax-how-to-group-measures-into-numeric-ranges/
@Anonymous,
You may take a look at the following post.
http://community.powerbi.com/t5/Desktop/Variable-Range-based-on-date-filter/m-p/449496#M208072
Sam, Thank you for posting that, but unfortunately that doesn't get me where I'm trying to go. In that measure that was suggested, it is calculating the count from a column, however, I dont have that data in a column, and it is aggregted in a measure. I have a measure that is calculating the Gross Profit %, and the Gross Profit % calculated in the measure is what I am trying to count. Is it possible to count the output of values of a measure?
@Anonymous,
You'll need to add a configuration table first.
https://www.daxpatterns.com/dynamic-segmentation/
User | Count |
---|---|
131 | |
71 | |
70 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |