Forum Discussion
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
- Anonymous1 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 LearnGiven 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
- lbendlinSuper User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- ND1979Frequent Visitor
Unfortunately, I can't share any records, due to data confidentiality, but I put in a picture the real relationships between the tables.
I can also use a generic solution that I will then adapt to the current situation.Thnaks a lot
- lbendlinSuper User
If you cannot provide meaningful sample data I cannot assist you.