Forum Discussion

RaptorFox's avatar
RaptorFox
Frequent Visitor
6 years ago
Solved

Calculate an Custom Column based on Values of another Table

Dear Community   I have an issue for displaying a progress of a project compared to its budget. I have two different tables, in one i have the budget, in the other i have all the actuals and the e...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi RaptorFox ,

     

    Try the measure.

    Measure = 
VAR x = 
CALCULATE(
    SUM('Time Bookings'[ExternalTotalAmount]),
    ALLEXCEPT('Project Data', 'Project Data'[ProjectNumber])
)
VAR y = 
DIVIDE(
    x,
    SELECTEDVALUE('Project Data'[Budget])
)
RETURN
y

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi RaptorFox ,

     

    In "Edit queries", we use M language which doesn't have the SUMX() function.

    Power Query M function reference 

    But we can also use M language grouping aggregation.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RaptorFox's avatar
RaptorFox
Frequent Visitor
6 years ago

parry2k Sure, thanks!

 

Sample Data for Table "Time Bookings"

 

ProjectNumberProjectNameEmployeeDateHoursExternalRateExternalTotalAmount
651232Project BI ImplementationPeter Miller 01.04.20202.5200500
651889ERP RenewalTony West03.04.202052001000
651232Project BI ImplementationCeline Wild20.04.20203190570
…      

 

Sample Data for Table "Project Data"

 

ProjectNumberProjectNameBudget
651232Project BI Implementation50000
651889ERP Renewal30000
…  

 

I expect a new row in the "Project Data" table which shows the calculated total amount of all the bookings in the "Time Bookings" table for the related project. The screenshot shows the underlying model and the relationship (the key is "ProjectNumber").

 

ProjectNumberProjectNameBudgetUsedAmountTotal
651232Project BI Implementation500001070
651889ERP Renewal300001000
…   

 

 

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

RaptorFox I assume you have one to many relationships between these two tables, just add the following measure and it should work

 

Used Amount Total = SUM ( TimeBookings[ExternalTotalAmount )

 

  • RaptorFox's avatar
    RaptorFox
    Frequent Visitor
    6 years ago

    parry2k Thanks! Yes, this measure works, i already had it. But now, i would like to somehow calculate and show the percentage between budget and the calculated total amount, therefore i wanted to have the amount directly in the 'Project Data' table.

     

    When i add the measure to the visual, i cannot show the proportion or percentage from the total amount to the budget, i just cannot get it done (might be and most probably is a usage issue):

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      RaptorFox what you are trying to show is not clear. for % you will do the following

       

       

      % = DIVIDE ( SUM( TableProject[Budget] ), [SUM Measure of spent amount] )

       

       

      what is the relevance of count in the visual?

    • v-lionel-msft's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi RaptorFox ,

       

      Try the measure.

      Measure = 
VAR x = 
CALCULATE(
    SUM('Time Bookings'[ExternalTotalAmount]),
    ALLEXCEPT('Project Data', 'Project Data'[ProjectNumber])
)
VAR y = 
DIVIDE(
    x,
    SELECTEDVALUE('Project Data'[Budget])
)
RETURN
y

       

      Best regards,
      Lionel Chen

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • RaptorFox's avatar
        RaptorFox
        Frequent Visitor
        6 years ago

        Hi v-lionel-msft 

         

        With this measure, i was able to at least solve the most pressing business need for information about the project progress, thanks! I know that i was not very clear in stating my expectations in the very beginning and created some confusion. Sorry for that!

         

        Just quickly back to my original question: Why am i not able to add a custom column summarizing all the "ExternalTotalAmount" for each project out of the data from the "Time Bookings" table and add those to the corresponding project in the my "Project Data" table by using the following query: