Calculated Fields and IF THEN Statements

Hello,
I have a PivotTable with a bunch of prices. I would like to organize the data into bands, so Band A - 'Eveything Below $10' and Band B - 'Everything above $10'. In my Calculated Field, how do I do - =IF('Price'<10,"Band A","Band B").


Example Data:

Product|Price
Car|5
House|20

Thanks,
Sharp

Originally Posted by xlnitwit

Hi,

Calculated Fields are always numeric values. If you want to do banding, you can either add a row field and group it, or it is often easier to use a lookup table and a formula in the source data.

I do have bands in the source data. However, they don't seem to be working.

For example, I have the following:

Industry|Client|Price|Band
Auto|FORD|100|'>100'
Auto|FORD|200|'>100'
Auto|Mazda|500|'>200'

When I apply my filters to the PivotTable, the price converges and groups the information. So, I have:

Industry|Client|Price|Band
Auto|FORD|300|'>100'
Auto|Mazda|500|'>200'

However, this is incorrect for FORD as it should be '>200'.

Thanks,
Sharp

So, in the source data, I simply use an IF statement to define each individual Price occurrence. The problem is when the PivoTable summarizes the total Price for FORD the Bands don't make sense

Unfortunately, no I don't. I googled around and it seems it's an add in for Excel 2013. Unfortunately, I cannot install anything additional to this computer. Is there a work around we could try?

Hmm, ideally I wanted to be able to use the Bands as a filter whereby I could display the price range needed. I guess there's no way around it...I could add it as a 'Row Field' and do 'Group By' but I actually have a bunch of bands to be applied as filters and not just the price one.

I was thinking about that but because my report has so many filters and so many options that the client can pick from..I'm not sure what to calculate the band on. For example, there are Open and Closed Accounts. Should I be applying the aggregate calculation on Open Only or Closed Only?

How do I go about this in PowerQuery?