Forum Discussion

ND1979's avatar
ND1979
Frequent Visitor
1 year ago
Solved

Calculate percent columns by several tables

Hello, I am new to Power BI environment, and I am learning on my own.

I would like to ask for your help to start creating my first reports.

 

In my case, I have 4 tables: Projects,Tasks,Timecards,User, where Projects are not directly connected to Timecards but they are directly connected to Tasks and then Taks is directly connected to Timecards

 

All the tables are linked by a correct relation.

 

 

Could you tell me the DAX functions to have, for each project in the Projects table, a new column in Project table that has the calculation of the percentage of hours consumed (in table Timecard) compared to the estimated hours (in table Projects) ?

 

For example:

Projects has:

PR1 -->72 hours

PR2 --> 16 hours

 

Tasks has:

Task PR1 --> TASK1_1

Task PR1 --> TASK1_2

Task PR2 --> TASK2_1

 

Timecard has:

PR1--> 60 (total of timecard for all users) (SUM of all timecards of the task for PR1) 

PR2 --> 7 (total of timecard for all users) (SUM of all timecards of the task for PR2) 

 

In the table Projects i'd like to have a new % column "PRC_CONSUMED" , that has the % of consumed time compared of the nominal project budget ,and use this value to create reports to monitor Project started, at limit of budget , etc...

 

Thanks in advance,

Domenico

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,ND1979 
    Thank you for your prompt response.

     

    I’m pleased to see that you are using the DAX query view for testing; it’s indeed a good method of validation. However, please note that the results returned in the query view are presented as a table, whereas the code I provided is context-based.

     

    Here are the results I obtained in the table view on my desktop:

    And here are the results from the DAX query view:

    For further details, please refer to:

    Deep dive into DAX query view and writing DAX queries | Microsoft Power BI Blog | Microsoft Power BI
    DAX query view - Power BI | Microsoft Learn

    Given the similarity to your erroneous results, I recommend creating a new calculated column.

    Even if you don’t use it later, you can easily delete it by right-clicking.

    Moreover, calculated tables and columns, including measures, do not affect the original data. They are not visible in Power Query, which serves as the best evidence of this. Therefore, you need not worry about creating unnecessary DAX expressions impacting your future work.

    I hope my response is clear enough to help you understand the reasons behind the issue.If you have any further questions, feel free to ask!

     

    Best Regards,

    Leroy Lu

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

     

10 Replies