Forum Discussion
Sum minimum values for all categories
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
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
2 Replies
- MattAllington
Community Champion
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
