Sum minimum values for all categories

[matthewpk]

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

[MattAllington]

Try this

sumx(values(table[category]), calculate(min(table[value])))

[matthewpk · Accepted 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