Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
What is a relatively simple chart in Excel is turning out ot be a real beast in Power BI.
The source table for the graph has a list of employees and how many hours worth of project they have to complete, for each week. As due dates are reached, project hours should drop off of the calculation for the next week.
The challenge for this chart in Power BI is finding a way to bucket the hours for each week correctly.
Pictured below is the desired output in Excel and the way the source data is structured. LOE(Total Level of Effort in Hours), Due Date, and Progress will drive the calculation:
if(Due Date>week category,(LOE*(1-progress)),0)
which is then summed up for each employee for each week.
Thank you in advance for your help!
Solved! Go to Solution.
You seem to be trying to do a lot of the heavy lifting of creating a visual table structure, context switching, and filtering in DAX. Let the visuals of PowerBI handle this work for you. In plain terms, what question is this calculated column supposed to be answering?
What is the table structure of this data? Without actually seeing it, something seems odd about it to me. Does the raw data that you're working from look like the excel sheet you posted before? If so, you may need to unpivot the date columns to get this into an easy to work with format.
As far as sharing the .pbix, just upload it to Google Drive/OneDrive/Dropbox/etc and share the link here.
Where are you currently at with this in PowerBI? What specific issues are you having other than it being a real beast in PBI?
As far as the total work remaining line, that would take the form of a cumulative total expression, but instead of using data from dates that were earlier, you sum data from dates that are later.
If you could share anonymized sample data or even better, an anonymized .pbix file, we could help you better.
Hi @Cmcmahan ,
I'll see what I can do about emailing the pbix. In the meantime, I made it to this code:
Remaining Effort = // Get the current day and employee for the current row VAR __week = 'Employee Calendar Combined'[Calendar] VAR _employee = 'Employee Calendar Combined'[Custom.FieldValuesAsText.Assigned_x0020_To] // Create Table Filtered by current employee VAR __table1 = FILTER('Employee Calendar Combined','Employee Calendar Combined'[Custom.FieldValuesAsText.Assigned_x0020_To]=_employee) // Create a table of all due dates greater than the current day VAR _table2 = FILTER(__table1, 'Employee Calendar Combined'[Modified ETC]>=__week) //sum work remaining based on number of days between selected date and due date VAR _total = sumx(_table2,([Due Date Fact Table.Custom]/max(1,('Employee Calendar Combined'[Modified ETC]-today()+1)))) return _total
But I think this is on the wrong track. The numbers I got were off. I've run myself around in circles for weeks on this, so I'm pretty much back to square one.
You seem to be trying to do a lot of the heavy lifting of creating a visual table structure, context switching, and filtering in DAX. Let the visuals of PowerBI handle this work for you. In plain terms, what question is this calculated column supposed to be answering?
What is the table structure of this data? Without actually seeing it, something seems odd about it to me. Does the raw data that you're working from look like the excel sheet you posted before? If so, you may need to unpivot the date columns to get this into an easy to work with format.
As far as sharing the .pbix, just upload it to Google Drive/OneDrive/Dropbox/etc and share the link here.
@Cmcmahan Yeah, it's unfortunate that it's prviledged info becuase I have no doubt we could figure it out together, but I think the best course of action will be for me to use the output data from the excel file as my data source for the visualization. It will make things a lot easier. Thanks for the help, I'm sorry I couldn't be more specific.
Is it possible to anonymize the info? Make a copy of a portion of the data set (maybe 2 or 3 projects worth?), keeping only fields needed for this calculation, replacing the names with stuff like ABC DEF, change the numbers, etc?
I still think what you want to accomplish should be VERY easy with Power BI, I'm just having trouble wrapping my head around how you have your tables set up.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |