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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
FJH2024
Frequent Visitor

Columns not calculating correctly - Filter issue?

Hi everyone,

 

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

 

FJH2024_1-1710417886281.png

 

 

 

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 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @FJH2024 ,

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:

vyifanwmsft_2-1710467221789.png

 

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.

vyifanwmsft_0-1710467088904.png

Final output:

vyifanwmsft_1-1710467174187.png

 

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.

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @FJH2024 ,

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:

vyifanwmsft_2-1710467221789.png

 

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.

vyifanwmsft_0-1710467088904.png

Final output:

vyifanwmsft_1-1710467174187.png

 

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.

 

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
Not applicable

Hi @FJH2024 ,

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.

vyifanwmsft_0-1710730890801.png


3. Modify the measure.

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

 

Final output:

vyifanwmsft_1-1710731099071.png

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.