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

[stuadz]

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
period	gbp value
1	12
1	74
2	36
2	54
3	74
3	34
4	65
4	33
5	75
5	87
5	54

dataset 2
period	target
1	85
2	85
3	90
4	90
5	95

table visual in power bi
Period	GBP Value	Target	Percentage to target	Value to target
1	86	85	101.18%	+1
2	90	85	105.88%	+5
3	108	90	120.00%	+18
4	98	90	108.89%	+8
5	216	95	227.37%	+121

Thanks

[VijayP]

@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

[stuadz]

Hi Vijay,

Thanks for your response.

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

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:

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

Thanks

[v-xuding-msft · 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]

+##;-##

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

[VijayP]

@stuadz 

Hope you have got resolution to your problem statement!