Forum Discussion
Helper IIIBring one total column from a table into another
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
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
12 Replies
- MFelix
Super User
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
VooseHi 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