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" )
3 Replies
- AlexisOlson
Super User
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" )- DIACHROMA
Thank you very much AlexisOlson , I just tested your formula and it works perfectly to meet my needs 🙂
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
