Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 externally charged amount (ExternalTotalAmount) per time booking.
I would like to add a custom column in the table "Project Data" to calculate percentage and so on to be displayed in a visual.
The following image displays the situation:
Does anyone have a tip how i can achieve my goal?
Thank you very much!
Solved! Go to Solution.
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.
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k Sure, thanks!
Sample Data for Table "Time Bookings"
ProjectNumber | ProjectName | Employee | Date | Hours | ExternalRate | ExternalTotalAmount |
651232 | Project BI Implementation | Peter Miller | 01.04.2020 | 2.5 | 200 | 500 |
651889 | ERP Renewal | Tony West | 03.04.2020 | 5 | 200 | 1000 |
651232 | Project BI Implementation | Celine Wild | 20.04.2020 | 3 | 190 | 570 |
… |
Sample Data for Table "Project Data"
ProjectNumber | ProjectName | Budget |
651232 | Project BI Implementation | 50000 |
651889 | ERP Renewal | 30000 |
… |
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").
ProjectNumber | ProjectName | Budget | UsedAmountTotal |
651232 | Project BI Implementation | 50000 | 1070 |
651889 | ERP Renewal | 30000 | 1000 |
… |
@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 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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):
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.
With this measure, i was able to at least solve the most pressing business need for information about the project progress, thanks! I know that i was not very clear in stating my expectations in the very beginning and created some confusion. Sorry for that!
Just quickly back to my original question: Why am i not able to add a custom column summarizing all the "ExternalTotalAmount" for each project out of the data from the "Time Bookings" table and add those to the corresponding project in the my "Project Data" table by using the following query:
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.
That's a very good and valuable hint. I managed to use the query to add a new table to my data set which is related 1:1 to the "Project Data" table. When i now try to add the summarized value to a new column within "Project Data", i undertstand that with the following query, the whole table is inserted as value of this new column, for each and every row in "Project Data":
= Table.AddColumn(#"Changed Type1", "AggregatedExternalAmount", each Table.Group(#"Time Bookings", {"ProjectNumber"},{{"Sum", each List.Sum([ExternalTotalAmount]),type number}}))
So i used the query to create a table on it's own:
What i tried to do now is adding a new row to "Project Data" and using the RELATED function to match the AggregatedExternalAmount to the corresponding project. But process-wise i think this is definitely not best practice, first to create a new table with all the sums for each project and then merge the "AggregatedExternalAmount" and the "Project Data" tables together. What is your take on that?
@RaptorFox what you are trying to show is not clear. for % you will do the following
% = DIVIDE ( SUM( TableProject[Budget] ), [SUM Measure of spent amount] )
what is the relevance of count in the visual?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |