Forum Discussion
Creating Graph that shows attainment on a target
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 |
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.
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.
6 Replies
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
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.
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.
This is workable too. Thank ypou so much!!!!
