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,...
Need Help
  • 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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

ND1979's avatar
ND1979
Frequent Visitor
1 year ago

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • ND1979's avatar
      ND1979
      Frequent Visitor
      1 year ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.