why is my measure calculation not happy when I add it to my table visual

[rico343]

I have a requirement to calculate a data column based on a couple parameter values.  When I do this and add the measure to my data table it does not display properly with the rest of the data.  

Here is the measure formula:

RequiredAdjustment40 = SUM(Hours[Calculated_Remaining])-(Headcount[Headcount Value])*('Monday 40'[Monday 40 Value])

Calculated_Remaining is the first data column shown in the table.

As you can see below.  My calculation is correct as shown on the right when the week is selected from the data table.  However, when I add the measure to the table as a new column it adds a day row and the calculation is not correct.  What is wrong?

[DivkLearner]

Hello @rico343, 

To work on it further, can you please share the details of 

Headcount[Headcount Value]) &

('Monday 40'[Monday 40 Value]). So we can check and give more details. 

Regards, 

DivKlearner, A bit forward daily. 

Link to my channel on Power BI.

https://www.youtube.com/watch?v=aADx2sPA2D0&list=PLDUIC70xSiVQQpfqGhOjBVDF8IBP2k6ob

[rico343]

It appears to have to do with the '-' subtraction.  if this is changed to * or / it displays properly.  How do I make the formula work properly?

RequiredAdjustment40 = SUM(Hours[Calculated_Remaining])-(Headcount[Headcount Value])*('Monday 40'[Monday 40 Value])

This also results in the same issue when just using a supplied number.

RequiredAdjustment40 = SUM(Hours[Calculated_Remaining])-50

And why do these work?

RequiredAdjustment40 = SUM(Hours[Calculated_Remaining])/50

RequiredAdjustment40 = SUM(Hours[Calculated_Remaining])*50

UGH!  I feel like this should be easy.

[DivkLearner]

Hello @rico343, 

Thank you for your response! I tried applying the same approach in my file, but whenever I add the Measure, it automatically includes dates from January 1st to December 31st for the year. I thought, can't we try using a Custom Column? Here’s what I did: please find the screenshots below. With a Custom Column, it doesn’t add extra rows when performing Sum, Difference, or Multiplication calculations, whereas with Measure, it does.

The Screenshot with Custom Column:

The Screenshot with Custom Measure and Custom Column:

It seems like there might be a bug here. In the meantime, wouldn’t using the Custom Column resolve the issue for you?

Regards, 

DivKlearner, A bit forward daily. 

Link to my channel on Power BI.

https://www.youtube.com/watch?v=aADx2sPA2D0&list=PLDUIC70xSiVQQpfqGhOjBVDF8IBP2k6ob

[v-zhangtin-msft]

Hi, @rico343 

The placement of parentheses can affect the calculation. In your formula, the multiplication is performed first due to the parentheses. Ensure that this is the intended order of operations.

Break down the formula into smaller parts to identify where the issue might be occurring. Create separate measures for each part of the formula and check their values.

Measure1 = SUM(Hours[Calculated_Remaining])

Measure2 = Headcount[Headcount Value] * 'Monday 40'[Monday 40 Value]

RequiredAdjustment40 = Measure1 - Measure2

Best Regards,

Community Support Team _Charlotte

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

[rico343]

I tried that as well.  The data is there and the calculation is correct, the issue is that when I use subtraction or addition in the formula it adds additional date rows to the displayed table.  In my first image the dates are my calculated 'start of week' field.  This works great until I put in a measure with the subtraction or addition in the formula.  I'm not sure why it does that.  It does not add the additional dates when I change the formula to multiplication or addition.  
I was able to work around this by filtering out the data fields that are blank for the additional date rows.  

[v-zhangtin-msft]

Hi, @rico343 

Can you provide more example data or example files? Please remove any sensitive data in advance.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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

[rico343]

Sure the difference is that those two values are parameter values that are supplied on the page by the user, and they can change as needed to adjust the data in the report.  They are not apart of the original table that was imported, so there is no relationship to make.  I just need to use these values to recalculate the original data and have it display in my table.