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.
Hello, I have a Project Report that I am having to manually manipulate in order to roll up the dollar values because of multiple START & FINISH dates within a Project Release.
Based on the examples below (*includes Current State & Desired State), I would like to introduce logic/condtion that can accomplish the Desired State in the attachment. In order to get to the desired state I need to introduce Date MIN/MAX, however I need the Date MIN/MAX to use the following logic:
MIN/MAX Start/Finish Dates by unique Department & associated unique release.
The goal is to roll up the dollar values at unique Department & unique Release and display the MIN Start Date & MAX Finish Date. Please let me know if any clarification is needed.
Thanks
Byron
Solved! Go to Solution.
Hi @bmdailey,
Based on your description, I assume your resource sample data is the same. Because your sample table is shown in screenshot, I can't copy, so I typed some part of it for reference.
Then click "New Table" under Modeling on home page. Type the following formula and you will get the table below.
NewTable = SUMMARIZE ( Test, Test[Department], Test[Release], "Budget", SUM ( Test[Budget] ), "Actual", SUM ( Test[Actual] ), "Variance", SUM ( Test[Variance] ), "Start Date", MIN ( Test[Start Date] ), "Finish Date", MAX ( Test[Finish Date] ) )
Finally, you can create a table visual, select all the columns to display as follows.
Best Regards,
Angelia
If I understand your challenge correctly this is actually super simple to achieve. Import your current state data. Make sure the data types come out correctly. Create a table visual and drop all your columns in the Values in desired order. Then use the drop-down menu for each of the columns in the Values bucket (see pic below) of the visual to choose the appropriate aggregation for Budget, Actual, Variance, and the dates (MAX/MIN).
Erik, thanks for the suggestion. I believe I've tried this method and had an issue with repetitive lines (*opposed to the value roll up by Department, Release). It's been some time ago so I will give it a shot and let you what I find out.
Thanks again for the suggestion....results to follow
Byron
Hi @bmdailey,
Based on your description, I assume your resource sample data is the same. Because your sample table is shown in screenshot, I can't copy, so I typed some part of it for reference.
Then click "New Table" under Modeling on home page. Type the following formula and you will get the table below.
NewTable = SUMMARIZE ( Test, Test[Department], Test[Release], "Budget", SUM ( Test[Budget] ), "Actual", SUM ( Test[Actual] ), "Variance", SUM ( Test[Variance] ), "Start Date", MIN ( Test[Start Date] ), "Finish Date", MAX ( Test[Finish Date] ) )
Finally, you can create a table visual, select all the columns to display as follows.
Best Regards,
Angelia
Sure thing. BTW that's what the aggregration is for, to avoid repetitive lines and instead get a sum, an average, a min or max etc. Good luck, let us know how it goes.
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 |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |