Getting a Maximum budget - Sum by a category - Help

[Anonymous]

Hello!

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

Value Spent	Category	Max. for the Classification
10.000	HR	50.000
20.000	HR	50.000
5.000	Orange	6.000
2.000	Orange	6.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

[sunny_talwar]

May be this

Sum(Aggr(Max, Category))

[sunny_talwar]

Or this

Sum(Max)/Count(DISTINCT Category)

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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!