Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help in creating Excel chart in Power BI

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!

 

Desired OutputDesired OutputSource Data LayoutSource Data Layout

1 ACCEPTED 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.  

 

View solution in original post

5 REPLIES 5
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

Anonymous
Not applicable

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.  

 

Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.