Creating Graph that shows attainment on a target

[norken20]

Hi All,

I just want to seek help on what is the best way to do this in power BI.

I have two tables as shown below. What I want is to create a stacked bar chart that will reflect the target (table 1) and the attainment if each personnel (posted status only). Your help will be very much appreciated. Thanks.

Personnel	Target Sales
A	500
B	400
C	300
D	200
E	100

Personnel	Status	Sales Value
A	Posted	20
B	Posted	40
C	Not Posted	60
D	Posted	20
E	Posted	50
E	Posted	60
D	Not Posted	10
D	Posted	40
A	Not Posted	20
B	Not Posted	12
B	Not Posted	10

[rajulshah · Accepted solution]

Hello @norken20,

Do you want something like this?

[rajulshah · Accepted solution]

Hello @norken20,

You can create a measure as below:

Target yet to achieve = 
VAR SalesValue = CALCULATE(SUM(Sales[Sales Value]),Sales[Status]="Posted")
VAR Target = SUM(TargetSales[Target Sales ])
VAR TargetToAchieve = Target - SalesValue
RETURN IF(TargetToAchieve<0,0,TargetToAchieve)

And use Sales and This measure in a stacked chart.

Let me know if this didn't help.

[Anonymous · Accepted solution]

Hi @norken20 ,

Please create two measures as below and add then to visual.

current attainment = CALCULATE(SUM('Table (2)'[Sales Value]),FILTER('Table (2)','Table (2)'[Status]="Posted"))

sales target = 
VAR target = SELECTEDVALUE('Table'[Target Sales ])-[current attainment]
return
IF(target<0,0,target)

Result would be shown as below.

 Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[norken20]

This is workable too. Thank ypou so much!!!!

[rajulshah · Accepted solution]

Hello @norken20,

Do you want something like this?

[norken20]

Thanks man. The idea is what I'm expecting 🙂 But do you think it's possible to have the sample graph below? Where the blue shade is the total sales target while the black is the current attainment which are the posted only. Not posted in the table will not be included. Thanks

Expected Graph

[rajulshah · Accepted solution]

Hello @norken20,

You can create a measure as below:

Target yet to achieve = 
VAR SalesValue = CALCULATE(SUM(Sales[Sales Value]),Sales[Status]="Posted")
VAR Target = SUM(TargetSales[Target Sales ])
VAR TargetToAchieve = Target - SalesValue
RETURN IF(TargetToAchieve<0,0,TargetToAchieve)

And use Sales and This measure in a stacked chart.

Let me know if this didn't help.

[norken20]

Thank you man. Your formula is very helpful and was able to modify it to display what I wanted. Thank you so much 🙂