Bring one total column from a table into another

[Voose]

Hi Guys,

Feel like I have a basic question here but just unsure how I can get this done in my head. In one table i have my target utilisation by region and by Month:

Target Utilisation	Region	Attribute
74.75	Americas	Mar
65	Americas	Apr
74.75	Americas	May
71.5	Americas	Jun
68.25	Americas	Jul
74.75	Americas	Aug
68.25	Americas	Sep
71.5	Americas	Oct
71.5	Americas	Nov
68.25	Americas	Dec
74.75	Americas	Jan
65	Americas	Feb
71.5	Americas	Mar2
71.5	APAC	Mar
65	APAC	Apr
74.75	APAC	May
65	APAC	Jun
74.75	APAC	Jul
71.5	APAC	Aug
68.25	APAC	Sep
74.75	APAC	Oct
68.25	APAC	Nov
71.5	APAC	Dec
71.5	APAC	Jan
68.25	APAC	Feb
74.75	APAC	Jan2
132.25	EMEA	Mar
115	EMEA	Apr
132.25	EMEA	May
126.5	EMEA	Jun
120.75	EMEA	Jul
132.25	EMEA	Aug
120.75	EMEA	Sep
126.5	EMEA	Oct
126.5	EMEA	Nov
120.75	EMEA	Dec
132.25	EMEA	Jan
115	EMEA	Feb
126.5	EMEA	Mar2

In another table i have my actual data by month and region as well:

Attribute	Region	Days Worked	Total Utilisation
Total	APAC	395
Total	EMEA	499
Total	Americas	109
Mar	APAC	80
Mar	EMEA	97
Mar	Americas	5
Apr	APAC	81
Apr	EMEA	90
Apr	Americas	38
May	APAC	55
May	EMEA	80
May	Americas	35
Jun	APAC	18
Jun	EMEA	70
Jun	Americas	23
Jul	APAC	10
Jul	EMEA	42
Jul	Americas	4
Aug	APAC	30
Aug	EMEA	32
Aug	Americas	2
Sep	APAC	30
Sep	EMEA	32
Sep	Americas	2
Oct	APAC	30
Oct	EMEA	26
Oct	Americas	2
Nov	APAC	30
Nov	EMEA	35
Nov	Americas	2
Dec	APAC	30
Dec	EMEA	26
Dec	Americas	2

I want to basically do a % of my actual vs a % of what is in my target column (the first table).

Let me know any thoughts or any easier ways of doing this I was planning on trying to pull the target column into the second tables then just creating a calculated column to bring in the value... having a bit of a mind fart let me know what you guys think.

Thanks

Voose

[MFelix · Accepted solution]

Hi @Voose,

Try to make a ID column in each of the table composed by region and month, them do a relation between the two table with that ID and you can the make a Column or use the values from the two table directly.

the Column will look something like this 

Column = CALCULATE(SUM(Target[Target Utilisation]);RELATEDTABLE(Target))

Regards

MFelix 

[Voose]

Hi @MFelix,

Thanks for the quick response, i've created the ID as suggested:

Attribute	Region	Days Worked	ID Creation	Target Utilisation
Total	APAC	395	TotalAPAC	#ERROR
Total	EMEA	499	TotalEMEA	#ERROR
Total	Americas	109	TotalAmericas	#ERROR
Mar	APAC	80	MarAPAC	#ERROR
Mar	EMEA	97	MarEMEA	#ERROR
Mar	Americas	5	MarAmericas	#ERROR
Apr	APAC	81	AprAPAC	#ERROR
Apr	EMEA	90	AprEMEA	#ERROR
Apr	Americas	38	AprAmericas	#ERROR
May	APAC	55	MayAPAC	#ERROR
May	EMEA	80	MayEMEA	#ERROR
May	Americas	35	MayAmericas	#ERROR
Jun	APAC	18	JunAPAC	#ERROR
Jun	EMEA	70	JunEMEA	#ERROR
Jun	Americas	23	JunAmericas	#ERROR
Jul	APAC	10	JulAPAC	#ERROR
Jul	EMEA	42	JulEMEA	#ERROR
Jul	Americas	4	JulAmericas	#ERROR
Aug	APAC	30	AugAPAC	#ERROR
Aug	EMEA	32	AugEMEA	#ERROR
Aug	Americas	2	AugAmericas	#ERROR
Sep	APAC	30	SepAPAC	#ERROR
Sep	EMEA	32	SepEMEA	#ERROR
Sep	Americas	2	SepAmericas	#ERROR
Oct	APAC	30	OctAPAC	#ERROR
Oct	EMEA	26	OctEMEA	#ERROR
Oct	Americas	2	OctAmericas	#ERROR
Nov	APAC	30	NovAPAC	#ERROR
Nov	EMEA	35	NovEMEA	#ERROR
Nov	Americas	2	NovAmericas	#ERROR
Dec	APAC	30	DecAPAC	#ERROR
Dec	EMEA	26	DecEMEA	#ERROR
Dec	Americas	2	DecAmericas	#ERROR

The above is the current output and the formula within the last column is as follows -> Target Utilisation = calculate(sum('Summarised Tables Month Ultilisation'[Target Utilisation]),RELATED('Summarised Tables Month Ultilisation'[Target Utilisation]))

Unsure why I'm not getting a value back.

Let me know!

[MFelix]

Hi @Voose,

In the related you can only refer to the table not a colum so you formula should be:

Target Utilisation = calculate(sum('Summarised Tables Month Ultilisation'[Target Utilisation]),RELATED('Summarised Tables Month Ultilisation))

You are already refering to the column you want in the sum function.

MFelix

[Voose]

Hi @MFelix,

Nailed it, we needed the relatedtable function 🙂 

Thanks for your help buddy!!

Voose

[Voose]

Hi @MFelix,

Trying to finish this off to create a % total on the end see below:

Attribute	Region	Days Worked	ID Creation	Total Working days by month and Region	% of target achieved
Total	APAC	395	TotalAPAC	0
Total	EMEA	499	TotalEMEA	0
Total	Americas	109	TotalAmericas	0
Mar	APAC	80	MarAPAC	71.5
Mar	EMEA	97	MarEMEA	132.25
Mar	Americas	5	MarAmericas	74.75
Apr	APAC	81	AprAPAC	65
Apr	EMEA	90	AprEMEA	115
Apr	Americas	38	AprAmericas	65
May	APAC	55	MayAPAC	74.75
May	EMEA	80	MayEMEA	132.25
May	Americas	35	MayAmericas	74.75
Jun	APAC	18	JunAPAC	65
Jun	EMEA	70	JunEMEA	126.5
Jun	Americas	23	JunAmericas	71.5
Jul	APAC	10	JulAPAC	74.75
Jul	EMEA	42	JulEMEA	120.75
Jul	Americas	4	JulAmericas	68.25
Aug	APAC	30	AugAPAC	71.5
Aug	EMEA	32	AugEMEA	132.25
Aug	Americas	2	AugAmericas	74.75
Sep	APAC	30	SepAPAC	68.25
Sep	EMEA	32	SepEMEA	120.75
Sep	Americas	2	SepAmericas	68.25
Oct	APAC	30	OctAPAC	74.75
Oct	EMEA	26	OctEMEA	126.5
Oct	Americas	2	OctAmericas	71.5
Nov	APAC	30	NovAPAC	68.25
Nov	EMEA	35	NovEMEA	126.5
Nov	Americas	2	NovAmericas	71.5
Dec	APAC	30	DecAPAC	71.5
Dec	EMEA	26	DecEMEA	120.75
Dec	Americas	2	DecAmericas	68.25

This is the formula im using -> % of target achieved = 'Summarised Actuals by Month'[Total Working days by month and Region]/'Summarised Actuals by Month'[Days Worked]

However I'm getting a circular dependancy error.... unsure why?

[MFelix]

Hi @Voose,

Keep it simple if you add the colum you need in your visual and chosse quick calc the Power BI will allow you to add the % over grand total,

Regards Mfelix

[Voose]

This is what my visual looks like currently, if I try and add a quick calc to the green line and calculate as % of grand total it gives me this :

Or if I do it the other way around I get a flat line also - the green line should be a % of the black line

Thanks for your patience 🙂

Voose

[MFelix]

The two line aren't in the same format since one is % and the other total numbers you need to have ine in coluns and the other in line choose the bar and line chart and it will work

Regards

Mfelix

[Voose]

Hi @MFelix,

Just nailed it with a line graph but using a measure that divides one against the other 🙂 thank you for your help! and appretiate your patience.

Thanks

Voose

[Voose]

Hi @MFelix,

So I want Days worked to be a % of Total working days by month for exmaple in this picture you can see that April is about 80% and in June its around 40%, I could like to see that on a single line graph,

Does that make sense? I shouldn't have 2 values on the graph, just 1 and that 1 value should be the % of these two against each other

Thanks

Voose

[MFelix]

@Voose,

Makes sense but that is different make a column that divides total days worked by total working days then format as % and use it in your chart.

Mfelix

[Voose]

Hi @MFelix,

Am now using  -> Target Utilisation = calculate(sum('Summarised Tables Month Ultilisation'[Target Utilisation]),RELATED('Summarised Tables Month Ultilisation'))

Still having the same error message... unsure why, i have a bi-directional 1-1 relationship in the model as well.

Thanks 

Voose