topic Re: DAX Calculated Column to identify price deviation in DAX Commands and Tips

DAX Calculated Column to identify price deviation

DIACHROMA — Sat, 04 Dec 2021 14:26:35 GMT

Hello everyone,

I need help identifying price deviations in my sales table.

In fact I would like to find a way to determine if a sale has been the subject of a promotion or on the contrary if the sale price is higher than usual.

Here is an example of what I would like to see:

Product	Retail Price	PROMO / OVERPRICE
A	10
A	10
A	10
A	10
A	5	PROMO
A	5	PROMO

B	20
B	20
B	25	OVERPRICE
B	20
B	20
B	10	PROMO
B	10	PROMO

I need this to be done in a calculated column to be able to use the "Promo / Overprice" column in a slicer.

I thought about creating a formula that identifies the most redundant price for each product and comparing the sales prices against that price.

But I have no idea of the syntax of my measurement to bring out the price that comes up most often by product.

Someone can help me ? Thanks in advance !!!

Pauline

Re: DAX Calculated Column to identify price deviation

AlexisOlson — Sat, 04 Dec 2021 17:23:14 GMT

The mode is the most frequently occurring value and DAX Patterns gives this general approach:

Mode := MINX ( TOPN ( 1, ADDCOLUMNS ( VALUES ( Data[Value] ), "Frequency", CALCULATE ( COUNT ( Data[Value] ) ) ), [Frequency], 0 ), Data[Value] )

This prior post gives further examples of how to apply this:
https://community.powerbi.com/t5/Desktop/Calculating-mode-of-a-measure/m-p/83116

Since you don't want the mode over the whole table (just the current product), you'll have to do a bit of additional filtering:

PROMO / OVERPRICE := VAR CurrProduct = Sales[Product] VAR FreqTable = GROUPBY ( FILTER ( Sales, Sales[Product] = CurrProduct ), Sales[Retail Price], "Frequency", SUMX ( CURRENTGROUP (), 1 ) ) VAR Mode = MINX ( TOPN ( 1, FreqTable, [Frequency] ), Sales[Retail Price] ) RETURN SWITCH ( TRUE (), Sales[Retail Price] < Mode, "PROMO", Sales[Retail Price] > Mode, "OVERPRICE" )

Re: DAX Calculated Column to identify price deviation

Anonymous — Tue, 07 Dec 2021 08:36:42 GMT

Hi DIACHROMA,

Did AlexisOlson 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: DAX Calculated Column to identify price deviation

DIACHROMA — Tue, 07 Dec 2021 14:28:02 GMT

Thank you very much AlexisOlson , I just tested your formula and it works perfectly to meet my needs 🙂