topic Re: Calculate percent columns by several tables in Developer

Calculate percent columns by several tables

ND1979 — Tue, 15 Oct 2024 13:43:54 GMT

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

Re: Calculate percent columns by several tables

lbendlin — Tue, 15 Oct 2024 22:58:53 GMT

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

Re: Calculate percent columns by several tables

ND1979 — Wed, 16 Oct 2024 11:57:54 GMT

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

Re: Calculate percent columns by several tables

lbendlin — Wed, 16 Oct 2024 12:03:37 GMT

If you cannot provide meaningful sample data I cannot assist you.

Re: Calculate percent columns by several tables

ND1979 — Wed, 16 Oct 2024 12:38:25 GMT

Hi lbendlin,

I I thought it was necessary the structure and the relation for a generic solution.

Thank you in cany case

Re: Calculate percent columns by several tables

Anonymous — Thu, 17 Oct 2024 09:41:21 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @ND1979

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Next create relationships between tables:

3.Create calculated column references:

I created the following calculated column in the timecard table to compute the time difference.

diff = DATEDIFF('Timecard'[start],'Timecard'[end],HOUR)

In the project table, I established a calculated column to facilitate the call and division.

sumofp = VAR cc = CALCULATE ( SUM ( 'Timecard'[diff] ), FILTER ( ALLSELECTED ( 'Timecard' ), 'Timecard'[Projects] = EARLIER ( 'Projects'[Projects ] ) ) ) RETURN DIVIDE ( cc, 'Projects'[hour] )

Subsequently, I modified the data type of the calculated column.

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

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.

Re: Calculate percent columns by several tables

ND1979 — Thu, 17 Oct 2024 13:46:11 GMT

Ciao and thank you so much for your help 👍

According to your example, I realized this code ( explain why), but I have a strange error:

EVALUATE

VAR cc =

CALCULATE (

SUM ( 'Timecard'[Hour_Time_Card] ),

FILTER (

ALLSELECTED ( 'Timecard' ),

'Timecard'[project_id] = EARLIER(ALL('Projects'[id]))

)

RETURN

DIVIDE ( cc, ALL('Projects'[estimated_hours]))

On the table Timecards, I have the field Hour_Time_Card that has the number of hour inserted for a specific task of the project , so I avoided to calculate dthe variable 'diff' and I used the existing field Hour_Time_Card.

I understood that the CALCULATE function sum all hours for a project_id , doing a join with the Projects table by the FILTER function.

The DIVIDE calclulate the percentage.

Now, I have this error and I don't understand what it means:

Query (7, 38) EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

Thnaks in advance for any help.

Regards,

Domenico

Re: Calculate percent columns by several tables

Anonymous — Fri, 18 Oct 2024 06:10:04 GMT

Hi, @ND1979

Based on the DAX you provided, I suspect the issue may stem from your use of the EARLIER() function alongside the ALL() function. I suggest removing the ALL() function to see if that resolves the problem.

It’s also worth considering that differing table relationships could directly impact the output results.

The reason I use EARLIER() is to establish a connection between the associated fields of the two tables, allowing for data grouping and summation.

EARLIER function (DAX) - DAX | Microsoft Learn

I hope my explanation is clear enough. If you have any further questions, please feel free to reach out to us.

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.

Re: Calculate percent columns by several tables

ND1979 — Mon, 21 Oct 2024 09:42:58 GMT

Hi @v-linyulu-msft ,

Thanks a lot for your feedback.

I used ALL because PowerBI DAX query said that the table\field does not exist and it's not true, as you can see in this screenhot. With ALL, i can select the table\field but then the result is always the same

Re: Calculate percent columns by several tables

Anonymous — Tue, 22 Oct 2024 02:49:17 GMT

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.

Re: Calculate percent columns by several tables

ND1979 — Tue, 22 Oct 2024 13:05:00 GMT

Dear @Anonymous ,

I don't know how to thank you!

I created a calculated column with the same code and the % are perfect. Now I have to study your link because It's not yeat clear .

Thanks!