Over and under budget calculations

[DuncanP]

Hi

I've been working on a problem and have hit a bit of a brick wall so I'm hoping someone can help, or perhaps point me in the right direction.

The problem seems quite simple in theory. I have multiple projects, each with a number of hours budget. People put time against the projects on a daily basis. All I want to know is the number of hours logged when the project is under budget, and the number of hours when it is over budget. I've got this working sliced at the project level, but need to get it working at other levels as well.

Essentially I need to perform the running total against the project, whilst slicing by other dimensions such as time or project member. At the moment the measures only work when the project is involved in the slice, which is not surprising really as I'll need to add something to get it to work with other dimensions.

I've created a mock pbix file here: https://1drv.ms/u/s!AvRL2YBsS-X9kfh4OUJxU3R9eFjAZg which has all the data needed to replicate the problem plus my attempts at creating some measures. You can see that the numbers for the projects work fine, but the grand totals are all incorrect.

I'm not looking for a solution, just a "have you tried ...?"

Can anyone help?

Cheers,

Duncan

[v-huizhn-msft]

Hi DuncanP,

After my study, I am still confuse about your question. I don’t find the slicer in given file. Could you please post your requirement and the measure with error? And I am very appreciate for you if you post the snapshot with slicer and report?

If you have any more questions, please don’t hesitate to ask.

Best Regards,
Angelia

[DuncanP]

Hi Angelia

Thank you for replying. I don't have an actual slicer in the report, I used the word slice as a generic term for filtering by different dimensions such as client, date, member and so on.

Duncan

[v-huizhn-msft]

@DuncanP  Sorry for my misunderstanding, you create your measure based on Project. So the grand totals are all incorrect when you when you caculte with other dimensions. I replace other filters for "Project", it is still not work. After my test, there sholud be a corresponding budget hours for each week, each client, each member.

If you have any question, please don't hesitate to ask. I’ll update here if there is some feedback.

Best Regards,
Angelia

[DuncanP]

Hi Angelia

Sorry for the delayed reply. I know what you mean, it's lacking the ability to calculate by anything other than project, which is expected. I need to find some way to line the hours up in order to allocate them by the other dimensions. I know how I'd do it in TSQL of course, but that's not much help here!

Any thoughts you (or anyone else reading this) might have on how to achieve this would be much appreciated.

Duncan

[DuncanP]

Ok, I've updated the solution to use a SUMX calculation which iterates over the projects. This now allows the values to be sliced by time.

Updated pbix here

In order to slice by member, which doesn't currently work, I will need to somehow allocate time between different members working on the same project on the same day.

Duncan

[v-huizhn-msft]

@DuncanP Yes, you'd better need to allocate time between different members according to your requirement. You should make a schedule for the members. Or you let the members record the labour time.

Best Regards,
Angelia