Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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?
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |