Forum Discussion

Voose's avatar
Voose
Icon for Helper III rankHelper III
9 years ago
Solved

Bring 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 Re...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    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's avatar
Voose
Icon for Helper III rankHelper III
9 years ago

Hi MFelix,

 

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

 

AttributeRegionDays WorkedID CreationTotal Working days by month and Region% of target achieved
TotalAPAC395TotalAPAC0 
TotalEMEA499TotalEMEA0 
TotalAmericas109TotalAmericas0 
MarAPAC80MarAPAC71.5 
MarEMEA97MarEMEA132.25 
MarAmericas5MarAmericas74.75 
AprAPAC81AprAPAC65 
AprEMEA90AprEMEA115 
AprAmericas38AprAmericas65 
MayAPAC55MayAPAC74.75 
MayEMEA80MayEMEA132.25 
MayAmericas35MayAmericas74.75 
JunAPAC18JunAPAC65 
JunEMEA70JunEMEA126.5 
JunAmericas23JunAmericas71.5 
JulAPAC10JulAPAC74.75 
JulEMEA42JulEMEA120.75 
JulAmericas4JulAmericas68.25 
AugAPAC30AugAPAC71.5 
AugEMEA32AugEMEA132.25 
AugAmericas2AugAmericas74.75 
SepAPAC30SepAPAC68.25 
SepEMEA32SepEMEA120.75 
SepAmericas2SepAmericas68.25 
OctAPAC30OctAPAC74.75 
OctEMEA26OctEMEA126.5 
OctAmericas2OctAmericas71.5 
NovAPAC30NovAPAC68.25 
NovEMEA35NovEMEA126.5 
NovAmericas2NovAmericas71.5 
DecAPAC30DecAPAC71.5 
DecEMEA26DecEMEA120.75 
DecAmericas2DecAmericas68.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's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

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's avatar
    Voose
    Icon for Helper III rankHelper III
    9 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      9 years ago
      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's avatar
        Voose
        Icon for Helper III rankHelper III
        9 years ago

        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