cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

SUM vs Target

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

AreaCost CentreCost DetailsMonthYear
SCSC1235000January2020
SCSC1234500February2020
SCSC1233500March2020
SCSC1236000April2020
SCSC1235500May2020
SCSC1235650June2020
SCSC1476400January2020
SCSC1476650February2020
SCSC1476900March2020
SCSC1477150April2020
SCSC1477400May2020
SCSC1477650June2020
FINFIN4567400January2020
FINFIN4567650February2020
FINFIN4567900March2020
FINFIN4568150April2020
FINFIN4568400May2020
FINFIN4568650June2020
FINFIN9638400January2020
FINFIN9638650February2020
FINFIN9638900March2020
FINFIN9639150April2020
FINFIN9639400May2020
FINFIN9639650June2020

 

Target

AreaCost CentreYear Target
SCSC12350000
SCSC14750000
FINFIN45650000
FINFIN96355000

 

Thank you in advance,

 

George

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @GeorgeGiannakis 

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 

SU18_powerbi_badge

View solution in original post

2 REPLIES 2
Super User III
Super User III

Hi @GeorgeGiannakis 

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 

SU18_powerbi_badge

View solution in original post

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.

 

GeorgeGiannakis_0-1605709781348.png

Thank you for your reply.

 

George

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors