Forum Discussion
Helper IIDAX Calculated Column to identify price deviation
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
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/83116Since 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" )
