Project Fee issue

[Craigc3814]

Good afternoon, I am working in Power BI Desktop and I am mostly comfortable in Power Query but I am not opposed to solving this through DAX either.

I am importing data from my companies financial software and it is causing a bit of an issue when I want to calculate the total fee of all projects that we have

We have a column called Project 1, Project 2, and Project 3, then a fee column.

Project 1 indicates that project number, Project 2 indicates the Task number, Project 3 indicates the sub task fee is just what it is the fee column

a row is made for each unique number between Project 1, 2, & 3

So if a project has tasks and subtasks a row will be created for each one and the fee is calculate per row

the problem I am having is it also creates a row for the entire project and in the fee column gives me the entire fee for the project so when I sum the fees it is doubling our fees

If every single project had tasks or subtasks I could easily just filter our that total row and I would have my number

The issue is every project doesnt. Some projects are just that a project with no tasks or subtasks and I need that total fee line.

Here is an Example

Project 1 Project 2 Project 3 Fee
1234 500
1235 600
1235 1 250
1235 1 1 250
1235 2 100

I want to remove that 1235 line with no tasks because the sum of all tasks get me to 600 but I do not want to remove the 1234 line

 

[Matt Allington]

You are going to have to build some logic to manage this. It seems to me that if there is a single row, then you want it. If there is more than one row for project 1, then you want to remove it. I suggest
break the problem into staging queries
do a group by project 1, count rows, sum the fee. If count = 1, then keep it
then go back to the original query (copy) and remove all rows with null in P2 and P3
append the two

 

[Craigc3814]

Yeah, that's pretty much what I did, I grouped on project one then did a distinct count. I can't remember how I did it from there but it was some form of an if formula that accounted for distinct count being greater than one and project 2 being blank and creating a new fee row from that. It then allowed me to filter out the blanks and gave me what I want.

I'll double check tomorrow, I definitely did not use any staging queries, I'm trying to keep the data as small as possible.

 

[Matt Allington]

staging queries have no impact on your data loaded.

 

[Craigc3814]

That's interesting, I always assumed if you made extra queries it had an impact on file size and refresh speed.