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.
Hi there,
New to PowerBI and first attempt at creating a report.
I have merged a few tables together to create the table below.
MergedTaskResource
rtask_prop_id:ID | Title | ComplianceAssetId | resource_name | resource_id | override_sell_rate | role_group | role_name | cost_rate_salary | region | num_hours | rtask_prop_idId | resource_phase_fk:ID | resource_phase_fkId | rtask_idId | onshore | description | rtask_id:ID | full_loaded_cost | total | business unit | comments | ID.1 | Modified | Created | GUID | rtask_prop_id | resource_phase_fk | rtask_id | Tasks.Id | Tasks.Title | Tasks.phase_fkId | Tasks.task_number | Tasks.start_date | Tasks.end_date | Tasks.task_name_calc | Tasks.AuthorId | Tasks.EditorId | DayDifference | AverageCost |
15 | null | null | NAME | 0 | Engineers | Engineer | Australia | 100 | 8 | 4 | 4 | 6 | onshore | Engineer | 6 | 300 | 26674.25 | null | null | 15 | 27/06/2019 10:55:53 PM | 27/06/2019 10:55:53 PM | 6 | new name PW | 4 | 1 | null | null | new name PW | 18 | 18 | null | 0 |
I am interested in producing a cost over time graph based on proposal id which is the first field as the table has many rows from many different IDs. I have managed to create a manual cost over time graph for a specific ID, which I have hardcoded in a filter " MergedTaskResource[rtask_prop_id:ID]="3" ".
DailyCost = CALCULATE( SUM(MergedTaskResource[AverageCost]) , FILTER( MergedTaskResource, AND([Adjusted Dates]<=MergedTaskResource[Tasks.end_date], [Adjusted Dates]>=MergedTaskResource[Tasks.start_date])), FILTER( MergedTaskResource, MergedTaskResource[rtask_prop_id:ID]="3"))
I was wondering if there was a way to generalise the filtering so it worked for proposal IDs of any value that the user could select in the report rather than changing the code above.
Is there also a dynamic way to create the start and end dates for the line chart, as the current proposal actually finishes on 30 July 2019 and starts 30th June but the line chart goes from earlier June to September.
To give a bit more context, I have attached the steps I've used to create the cumulative costs above.
To do the following, two calculated columns DayDifference and average daily cost was added to the table MergedTaskResources.
A separate Calender table was used to do the daily calculation, in this same table I have added a few more calculated columns to create the table below.
DailyCost
DailyCost = CALCULATE( SUM(MergedTaskResource[AverageCost]) , FILTER( MergedTaskResource, AND([Adjusted Dates]<=MergedTaskResource[Tasks.end_date], [Adjusted Dates]>=MergedTaskResource[Tasks.start_date])), FILTER( MergedTaskResource, MergedTaskResource[rtask_prop_id:ID]="3"))
Adjusted Dates
Adjusted Dates = 'Dates'[Date] - (10/24)
Cumulative Total
Cumulative Total = CALCULATE( SUM(Dates[DailyCost]), 'Dates'[Adjusted Dates] <= EARLIER ('Dates'[Adjusted Dates]))
Thanks!
Here is some sample data,
Id | Title | ComplianceAssetId | resource_name | resource_id | override_sell_rate | role_group | role_name | cost_rate_salary | region | num_hours | rtask_prop_idId | resource_phase_fk:ID | resource_phase_fkId | rtask_idId | onshore | description | rtask_id:ID | rtask_prop_id:ID | full_loaded_cost | total | business unit | comments | ID.1 | Modified | Created | GUID | rtask_prop_id | resource_phase_fk | rtask_id | Tasks.Id | Tasks.Title | Tasks.phase_fkId | Tasks.task_number | Tasks.start_date | Tasks.end_date | Tasks.task_name_calc | Tasks.AuthorId | Tasks.EditorId | DayDifference | AverageCost |
156 | null | null | 150 | Australia | 0 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 98.4 | 0 | test | 156 | 30/07/2019 1:43:24 AM | 23/07/2019 11:07:27 PM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 0 | |||||||||||
157 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 100.08 | 110.088 | null | 157 | 24/07/2019 11:00:05 PM | 23/07/2019 11:07:55 PM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 12.232 | |||||||||||
158 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 98.4 | 541.2 | null | 158 | 24/07/2019 10:59:06 PM | 24/07/2019 1:25:55 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 60.13333 | |||||||||||
161 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 100.08 | 550.44 | null | 161 | 24/07/2019 10:59:07 PM | 24/07/2019 1:40:06 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 61.16 | |||||||||||
163 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 100.08 | 550.44 | null | 163 | 24/07/2019 10:59:09 PM | 24/07/2019 2:20:47 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 61.16 | |||||||||||
168 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 100.08 | 550.44 | null | 168 | 24/07/2019 10:59:11 PM | 24/07/2019 4:46:51 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 61.16 | |||||||||||
169 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 27 | onshore | 27 | 3 | 100.08 | 110.088 | null | 169 | 24/07/2019 11:00:06 PM | 24/07/2019 4:50:20 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 12.232 | |||||||||||
400 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 28 | onshore | 28 | 3 | 98.4 | 108.24 | null | 400 | 31/07/2019 3:59:32 AM | 31/07/2019 3:59:32 AM | 28 | Task Two | 27 | 2 | 8/07/2019 2:00:00 PM | 14/07/2019 2:00:00 PM | Task Two | 13 | 13 | 7 | 15.46286 | |||||||||||
170 | null | null | 0 | Australia | 1 | 3 | 28 | 28 | 29 | onshore | 29 | 3 | 100.08 | 110.088 | null | 170 | 24/07/2019 11:00:09 PM | 24/07/2019 4:50:21 AM | 29 | Task One Phase Two | 28 | 1 | 10/07/2019 2:00:00 PM | 10/07/2019 2:00:00 PM | Task One Phase Two | 13 | 13 | 1 | 110.088 | |||||||||||
401 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 135 | onshore | 135 | 3 | 98.4 | 108.24 | null | 401 | 31/07/2019 3:59:33 AM | 31/07/2019 3:59:33 AM | 135 | Task 3 | 27 | 3 | 18/07/2019 2:00:00 PM | 30/07/2019 2:00:00 PM | Task 3 | 13 | 13 | 13 | 8.326154 | |||||||||||
156 | null | null | 150 | Australia | 0 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 98.4 | 0 | test | 156 | 30/07/2019 1:43:24 AM | 23/07/2019 11:07:27 PM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 0 | |||||||||||
157 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 100.08 | 110.088 | null | 157 | 24/07/2019 11:00:05 PM | 23/07/2019 11:07:55 PM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 12.232 | |||||||||||
158 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 98.4 | 541.2 | null | 158 | 24/07/2019 10:59:06 PM | 24/07/2019 1:25:55 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 60.13333 | |||||||||||
161 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 100.08 | 550.44 | null | 161 | 24/07/2019 10:59:07 PM | 24/07/2019 1:40:06 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 61.16 | |||||||||||
163 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 100.08 | 550.44 | null | 163 | 24/07/2019 10:59:09 PM | 24/07/2019 2:20:47 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 61.16 | |||||||||||
168 | null | null | 0 | Australia | 5 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 100.08 | 550.44 | null | 168 | 24/07/2019 10:59:11 PM | 24/07/2019 4:46:51 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 61.16 | |||||||||||
169 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 27 | onshore | 27 | 4 | 100.08 | 110.088 | null | 169 | 24/07/2019 11:00:06 PM | 24/07/2019 4:50:20 AM | 27 | Task One | 27 | 1 | 30/06/2019 2:00:00 PM | 8/07/2019 2:00:00 PM | Task One | 13 | 13 | 9 | 12.232 | |||||||||||
400 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 28 | onshore | 28 | 4 | 98.4 | 108.24 | null | 400 | 31/07/2019 3:59:32 AM | 31/07/2019 3:59:32 AM | 28 | Task Two | 27 | 2 | 8/07/2019 2:00:00 PM | 14/07/2019 2:00:00 PM | Task Two | 13 | 13 | 7 | 15.46286 | |||||||||||
170 | null | null | 0 | Australia | 1 | 3 | 28 | 28 | 29 | onshore | 29 | 4 | 100.08 | 110.088 | null | 170 | 24/07/2019 11:00:09 PM | 24/07/2019 4:50:21 AM | 29 | Task One Phase Two | 28 | 1 | 10/07/2019 2:00:00 PM | 10/07/2019 2:00:00 PM | Task One Phase Two | 13 | 13 | 1 | 110.088 | |||||||||||
401 | null | null | 0 | Australia | 1 | 3 | 27 | 27 | 135 | onshore | 135 | 4 | 98.4 | 108.24 | null | 401 | 31/07/2019 3:59:33 AM | 31/07/2019 3:59:33 AM | 135 | Task 3 | 27 | 3 | 18/07/2019 2:00:00 PM | 30/07/2019 2:00:00 PM | Task 3 | 13 | 13 | 13 | 8.326154 |
Solved! Go to Solution.
@Anonymous
You may use ALLSELECTED as a CALCULATE modifier and add an appropriate measure to Visual level filters to adjust the time axis.
@Anonymous
You may use ALLSELECTED as a CALCULATE modifier and add an appropriate measure to Visual level filters to adjust the time axis.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |