Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
You'll need to add a configuration table first.
https://www.daxpatterns.com/dynamic-segmentation/
Hi @wmeyer. 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/
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?
You'll need to add a configuration table first.
https://www.daxpatterns.com/dynamic-segmentation/
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
101 | |
97 | |
88 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |