topic What formula to use in DAX Commands and Tips

What formula to use

Anonymous — Sat, 04 Sep 2021 01:42:13 GMT

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.

Re: What formula to use

Greg_Deckler — Sat, 04 Sep 2021 03:14:55 GMT

Anonymous 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])

Re: What formula to use

VahidDM — Sat, 04 Sep 2021 07:36:36 GMT

Hi Anonymous

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 ✌️!!

Re: What formula to use

Anonymous — Mon, 06 Sep 2021 06:36:23 GMT

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.

Re: What formula to use

VahidDM — Mon, 06 Sep 2021 07:29:43 GMT

Hi Anonymous

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 !!