cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Adding a column to a table visual to show percentage to target for period

Hi,

 

Any help is much appreciated, I'm new to powerBI and trying to learn as I am completing report requests.

 

I have two datasests, one is basic sales data with a period assigned to each sales line and the other is table showing the sales targets for each period.

 

I have created a table visual that sums the total sales against each period and pulled through the target for each period.  What I need is another column in the table visual that shows the total sales for that period as a percentage of the target.  I know I need to create a measure but I just can't find a tutorial to specifically create the percentage for each period.  I also need to add a column that subtracts the sum of sales for each period from the target for that period and display it with either a "+" or "-" dependant on whether the target was achived.

 

Below, I've tried to show what I'm trying to achieve:

 

dataset 1 
periodgbp value
112
174
236
254
374
334
465
433
575
587
554

 

dataset 2 
periodtarget
185
285
390
490
595

 

 

table visual in power bi    
PeriodGBP ValueTargetPercentage to targetValue to target
18685101.18%+1
29085105.88%+5
310890120.00%+18
49890108.89%+8
521695227.37%+121

 

 

Thanks

1 ACCEPTED SOLUTION

Hi @stuadz ,

 

@VijayP 's answer is right. I add a way about formatting "Value to target". Please try like this:

 

Measures:

GBP = CALCULATE(SUM('Table 1'[gbp value]))
Target = CALCULATE(SUM('Table 2'[target]),FILTER('Table 2','Table 2'[period] in VALUES('Table 1'[period])))
Percentage to target = DIVIDE([GBP],[Target])
Value to target = [GBP] - [Target]

 

2.PNG

 

 

+##;-##

1.PNG

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

 

 

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

View solution in original post

4 REPLIES 4
Super User I
Super User I

@stuadz 

For a Variance Column use the simple Formula

"=Sales - Target" ( I assume you name the column as "Variance") then

for % of Variance

Second Column Name = divide(Variance,Target,0).

 

Hoping that your Table is ready with Sales and Target

 

Please let me know if this solves your  problem statment

 

Regards

Vijay Perepa





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Vijay,

 

Thanks for your response.

 

This is the formula for the first part (subtraction):

 

stuadz_2-1600435860722.png

 

This is what I'm getting currently which is inccorrect: (EDIT) this is incorrect because I wasnt to see the total value for each period subtracted from the target like below:

 

stuadz_0-1600436053986.png

 

 

stuadz_0-1600435811728.png

 

I think I am selecting the wrong calculation on the target:

 

stuadz_3-1600435915475.png

 

Thanks

 

Hi @stuadz ,

 

@VijayP 's answer is right. I add a way about formatting "Value to target". Please try like this:

 

Measures:

GBP = CALCULATE(SUM('Table 1'[gbp value]))
Target = CALCULATE(SUM('Table 2'[target]),FILTER('Table 2','Table 2'[period] in VALUES('Table 1'[period])))
Percentage to target = DIVIDE([GBP],[Target])
Value to target = [GBP] - [Target]

 

2.PNG

 

 

+##;-##

1.PNG

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

 

 

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

View solution in original post

@stuadz 

Hope you have got resolution to your problem statement!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

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!.

Top Solution Authors
Top Kudoed Authors