Help with expression

[jamelmessaoud]

Hi guys,

This should be simple but I'm struggling to write the correct expression!

I have two measures.

• Sum([Suppliers FTL Cost])
• Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate])

I want to do a count of a field where the first measure is greater than the 2nd measure (and vice versa). 

Count Suppliers where Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]) is basically what i want to achieve

Can anyone help?

Thanks

J

[jamelmessaoud]

You are right Sunny it works like this.  Sorry I was using the wrong field - didn't want supplier.  Needed to use Sub Contractor

sum(Aggr(If(Sum([Suppliers FTL Cost]) > Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]), 1, 0), [Sub Contractor]))

If i try the other way though

sum(Aggr(If(Sum({<[Sub Contractor Type] = {'Base Rate'}>}[Sub Contractor Rate]) > Sum([Suppliers FTL Cost]), 1, 0), [Sub Contractor]))

The result is always zero.  Do you think it will not work the other way?

Thanks

J

[sunny_talwar]

Difficult to say... may be you can share a sample to check?

[jamelmessaoud]

It's ok Sunny.  Your expression is working just fine.  I need to think about how I want this to work

Thanks for your assistance

[sunny_talwar]

Awesome, I am glad it finally worked.

Best,

Sunny