SUM vs Target

[Anonymous]

Hello Community,

First of all, I hope all of you are safe.

I would appreciated it if you could help me with the below task.

I want to do a comparison between the aggregated cost details per area per cost centre versus the year target of the same area/cost centre combo. I personally tried two different approaches without any success.

1) Created an area/cost centre combo column at both tables, which was then used to merge the two tables. Problem faced during comparison was that the year target was wrong (multiplied by number of rows)

2) Based on the above combo column, I created a relationship between these two tables, but I could not generate any result.

My goal is to create a gauge diagram that will show each area's/cost centre's performance versus the related target.

Could you provide help on how to approach this challenge?

Cost

Area	Cost Centre	Cost Details	Month	Year
SC	SC123	5000	January	2020
SC	SC123	4500	February	2020
SC	SC123	3500	March	2020
SC	SC123	6000	April	2020
SC	SC123	5500	May	2020
SC	SC123	5650	June	2020
SC	SC147	6400	January	2020
SC	SC147	6650	February	2020
SC	SC147	6900	March	2020
SC	SC147	7150	April	2020
SC	SC147	7400	May	2020
SC	SC147	7650	June	2020
FIN	FIN456	7400	January	2020
FIN	FIN456	7650	February	2020
FIN	FIN456	7900	March	2020
FIN	FIN456	8150	April	2020
FIN	FIN456	8400	May	2020
FIN	FIN456	8650	June	2020
FIN	FIN963	8400	January	2020
FIN	FIN963	8650	February	2020
FIN	FIN963	8900	March	2020
FIN	FIN963	9150	April	2020
FIN	FIN963	9400	May	2020
FIN	FIN963	9650	June	2020

Target

Area	Cost Centre	Year Target
SC	SC123	50000
SC	SC147	50000
FIN	FIN456	50000
FIN	FIN963	55000

Thank you in advance,

George

[AlB · Accepted solution]

Hi @Anonymous 

Something like this? See it all at work in the attached file.

It has a relationship like you described and you can choose what cost centre(s) to show in the slicer. You can modify it if you want a max value different from the target

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hello @AlB ,

Thank you for your reply , that was very helpful.

You also gave me the opportunity to dig a little deeper and recreate the same graphs by using measures.

The tricky one was to capture the correct target per area/cost centre.

This is the measure I came up with.

Target = CALCULATE(MAX(Target[Year Target]))

Once I sorted this out, I was then able to create the below graph.

Thank you for your reply.

George