5 Replies Latest reply: Jun 12, 2017 3:45 PM by Audrey Fischer

# Getting a Maximum budget - Sum by a category - Help

Hello!

I need a huge help.. I have the following data:

Value SpentCategoryMax. for the Classification
10.000HR50.000
20.000HR50.000
5.000Orange6.000
2.000Orange6.000

I need a way to calculate the overall spent X maximum.. in this case, I needed to get to:

Spent: 37.000

Maxium: 56.000

The simplest way I thought of it was to do:

Sum(Value spent)

Sum(Max)/Count(Max)

However, that doesnt work for the different values.. anyone has easy way to get the sum of my maximum value? I considered doing a Sum if for each category, but it seemed such a dumb way to do this.

Thanks,

Audrey

• ###### Re: Getting a Maximum budget - Sum by a category - Help

May be this

Sum(Aggr(Max, Category))

• ###### Re: Getting a Maximum budget - Sum by a category - Help

Or this

Sum(Max)/Count(DISTINCT Category)

• ###### Re: Getting a Maximum budget - Sum by a category - Help

The outcome was:

period               spent                         max

 2017-jan R\$ 17.883.779,04 R\$ 419.792.066,85 2017-fev R\$ 11.400.290,56 R\$ 530.777.270,78 2017-mar R\$ 19.972.635,45 R\$ 413.504.165,97

I was supposed to be something around 14 mil in 2017-jan

• ###### Re: Getting a Maximum budget - Sum by a category - Help

Would it be possible to share a sample to look at?

• ###### Re: Getting a Maximum budget - Sum by a category - Help

Sure, I'll attach it here, but I believe it´s a math problem really.. Sum(Max)/Count(DISTINCT Category)

The ideal here is to sum individually.. like ((Sum(if(Max="Orange",Max,0) / sum(if(Max="Orange",1,0)) + (Sum(if(Category="HR",Max,0) / sum(if(Category="HR",1,0)))

But it would be a huge line, because I have like 10-12 category.

Tks!