What formula to use

[Yau0101]

Dear All,

I have two tables below and i want to split my price into 4 new columns:

1. v low price

2. low price

3. Avg price

4. High price

The price to be shown in each of these columns should be:
o Very Low:  if price < (average - $7.50) for that product, show the quantity, if not show "0"
o Low:  if price between (average - $7.50) and (average - $2.50) for that product, show the quantity, if not show "0"
o Average:  If price between (average - $2.50) and (average + $2.50), show the quantity, if not show "0"
o High: If price > (average + $2.50), show the quantity, if not show "0"

How do i calculate this? Please advise. 

[VahidDM · Accepted solution]

Hi @Yau0101 

Please donwload this file, I added all those columns to your table.

Download Link : https://gofile.io/d/L3yBiZ

Output:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!

[VahidDM · Accepted solution]

Hi @Yau0101 

Please donwload this file, I added all those columns to your table.

Download Link : https://gofile.io/d/L3yBiZ

Output:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos ✌️!!

[Yau0101]

Hi @VahidDM 

Thank you for the soultion. I have another question, i want to create new measure name "Price 1" and "volume". The "Price 1", i want to it to grab all the data from price column and the "volume" measure i want to grab data from quantity column. what formula should i use? Please advise. 

[VahidDM · Accepted solution]

Hi @Yau0101 

I did not get your question very well.

Do you want to have separate tables for Price and Quantity? then use 

Or do you want duplicate columns to indicate exact figures from Price and Quantity? if yes try these codes to add new columns:

Price 1 = 'Table'[price]

Volume = 'Table'[quantity]

If you need something else, share more details.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos !!

[Greg_Deckler]

@Yau0101 I think the first thing you need is a working average measure like:

Average Measure =
  VAR __Product = MAX('Table'[Product])
RETURN
  AVERAGEX(FILTER(ALL('Table'),[Product] = __Product),[price])