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.
Hello,
I'm looking for help in summing the lowest values for every category. I understand I can throw in all the data and then run a min formula, that will give me a minimum value for every aggregate or category but won't give me the sum of all the minimum values. The example is as follows:
- Using the min function and showing all data in a table, will show a grand total of 1 but will accurately show that A=1, B=2, C=4
- What I need is a grand total of 7 because the sum of all mins is a=1 + b=2 + c=4
I need this because I want to be able to show charts & graphs with this overall sum as opposed to having to walk through an entire table, this data set has a few 10s of thousands of line items.
Category Value
a 1
a 2
b 2
b 2
c 4
c 7
Solved! Go to Solution.
Hi Matt, thank you for replying.
Unfortunately, this didn't work for me, it provided the following result
Category Value Current Result Desired Result
a 1 1 1
a 2 2
b 2 2 2
b 2 2
c 4 4 4
c 7 7
I think I found the answer by using the following:
=IF( CALCULATE(min(table[value]), ALLEXCEPT(table[category])) <> table[value], CALCULATE(min(table[value]), ALLEXCEPT(table[category])), 0)
It still isn't ideal because there seem to be multiple min values in some categories and it sums all of them. I used other filters in the calculate formula to continue to exclude values. I might have to go to a rank function to make sure I don't double count minimum values
Try this
sumx(values(table[category]), calculate(min(table[value])))
Hi Matt, thank you for replying.
Unfortunately, this didn't work for me, it provided the following result
Category Value Current Result Desired Result
a 1 1 1
a 2 2
b 2 2 2
b 2 2
c 4 4 4
c 7 7
I think I found the answer by using the following:
=IF( CALCULATE(min(table[value]), ALLEXCEPT(table[category])) <> table[value], CALCULATE(min(table[value]), ALLEXCEPT(table[category])), 0)
It still isn't ideal because there seem to be multiple min values in some categories and it sums all of them. I used other filters in the calculate formula to continue to exclude values. I might have to go to a rank function to make sure I don't double count minimum values
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |