Columns not calculating correctly - Filter issue?

[Anonymous]

Hi everyone,

I have an issue where a table isn't calculating correctly:

As you can tell by the above screenshot, the figures in "Left in Budget" are currently incorrect, one showing -£27,246.67 when it should be £11,479.33 and the other showing £154,926.33 when it should be showing £201,737. The formula behind the Left in Budget is 'Current PO' - 'Chargeable Time Logged'.

The issue is that both lines/tables have the same information contained within them, however one line should show figures from before 01/02/2024, and the other should show after 01/02/2024. When the date filter is applied the Chargeable Time Logged figure will be altered, but the Left in Budget column remains unchanged (and so is treating the Chargeable Time Logged amount as the full £85,536.67 figure that is pre-filter). Does anyone know why it might be doing this? Please let me know if you require any more info!

Thanks so much for any and all help in advance 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your description, I have created some examples for you to solve your problem. Here are a few steps and notes to help solve this problem.

Sample data:

1. Add a measure. Ensure that "Left in Budget" is using a measure, if it is using a calculated column it will not dynamically update based on the filter context.

Left in Budget = 
SUM('Table'[Current PO]) - SUM('Table'[Chargeable Time Logged])

2. Add a DateTable to create a relationship with your data dates. Incorrect or missing relationships may cause the filter not to apply as expected.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous,

Thank you so much for your help on this! I'm pretty sure this is going to resolve my issue so I'm going to accept is as the solution, however could I ask you for just a bit more help please?

My 'Chargeable Time Logged' column is actually a measure as I need it to do the following calculation for me:

Chargeable Time Logged = SUM(Worklogs[TEMPO_BILLABLE_TIME])/3600*134

This is because 'Tempo Billable Time' is in seconds so I'm /3600 to make it hours and then * this by 134 to get my hourly chargeable rate. I think this is affecting the solution as I'm trying to do a measure for your solution but I can't include another measure in this (or at least it's not letting me, I might be doing something wrong!)

I do have a calculated colunm I'm using to create the hours:

Measure > Sum Billable1 = SUM(Worklogs[TEMPO_BILLABLE_TIME])
Calc Column > Chargeable Hours = 'Worklogs'[Sum Billable1]/3600

Could I maybe factor this column into the measure you ahve selected with a CALCULATE function attached to * it by 134? 

Thank you so much for your help in advance, I really do appreciate it!!

[Anonymous]

Hi @Anonymous ,

I would be happy to answer any further questions you may have, your data seems to be on two tables. You can follow the steps below:
1. You can indeed add the calculation column [Chargeable Hours] to convert the time as you have done and then *134 (hourly rate).

Chargeable Hours = 'worklog'[Chargeable Time Logged]/3600*134

2. Create a many-to-many relationship between the two tables.

3. Modify the measure.

Left in Budget = 
SUM('Table'[Current PO]) - SUM(worklog[Chargeable Hours])

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.