Extract a benchmark figuere from column and compare to the rest of the same column

[steven_dot]

Hello, 

Thank you for reading this post and please allow me to explain my problem and hopefully you can help. 

I have a data set with two columns. In every data set there is a 'benchmark' figure I need to compare this benchmark to the other values and apply some if logic to see how far the difference is from the benchmark.

You will see from the data below (left side) that I have to compare each value to the benchmark and then give an output based on some rules (right side). The middle section is how I see the problem working. This would be to 1st compare each row with the benchmark and then apply to the if logic to see how large the difference is from the benchmark. Depending on the size of the differecne and if positive of negative a value would be applied.

Please see an example data set below and thank you very much

Product	Sales		Benchmark	Difference	Output		Rules	Output
Umbrella	9		10	-1	2		If greater than + 2	4
Chair	10		10	0	2		If between 1 & 2	3
Table	14		10	4	4		If between 1 -& -1	2
Stool	7		10	-3	0		If between -1 & -2	1
Benchmark	12		10	2	1		If lower than - 2	0

[harshnathani · Accepted solution]

Hi @steven_dot ,

Create a Calculated Column

Final Score =

var _diff = 'Table'[Sales] - 'Table'[Benchmark]

RETURN

SWITCH(

True(),

_diff > 2 , 4,

_diff <=2 && _diff >1 ,3,

_diff <=1 && _diff >-1 , 2,

_diff <=-1 && _diff >-2 , 1,

0

)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[steven_dot]

hello,

Thank you very much for your reply. That is very helpful!

First, there was an error in the dataset the benchmark is in the last row should be '10' and not '12'. The issue I have is that I don't have the 'Benchmark' column already . How do I create this from the 'benchmark' row and then applying this to all row, thus creating the 'benchmark' column which I can compare?

Thank you very much

[harshnathani]

Hi @steven_dot ,

Sorry, i did not understand by what you mean to create  a benchmark column.

You can add it to the dataset when you load data in Power BI or you can create a column from example.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[steven_dot]

Thank you so much - you're a hero

[harshnathani]

Hi @steven_dot ,

Thanks 🙂

If my answer  is a solution to the problem posted, please mark it as solution.

Thanks

Harsh