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.

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

RaptorFox very hard to follow your excel sheet, can you past sample data in a table format and explain what is your expected result.

 

Please share data in excel or share pbix file using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

  • 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):