DAX Calculated Column to identify price deviation

[DIACHROMA]

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

[v-shex-msft]

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

[AlexisOlson · Accepted solution]

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"
    )

[DIACHROMA]

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