cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Incorrect Sum of Rows

Hi All,

I have a table like below. Now when i use the inbuilt summarisation function for the column, i was getting a weird Total. So i created a measure to just sum up headcount and i'm getting odd sums. Basically, it should've just returned the Headcount number, but instead i think it's summing up other values for the months as there are duplicate dates.

Does anyone know how i can get it to only sum up for that Fiscal Month + Fiscal Date combo?

 Fiscal Month Fiscal Date Headcount Sum of Headcount Aug-21 Jul-21 5 15 Aug-21 Aug-21 5 20 Aug-21 Sept-21 5 24 Aug-21 Oct-21 5 24 Aug-21 Nov-21 5 30 Aug-21 Dec-21 4 35 Aug-21 Jan-22 3 65 Aug-21 Feb-22 5 67 Aug-21 Mar-22 4 88 Aug-21 Apr-22 3 89 Aug-21 May-22 4 90 Aug-21 Jun-22 3 95
1 ACCEPTED SOLUTION
Community Support

Hi  @Andrea_Jess ,

According to your description, the problem may be caused by the wrong statistics of the formula. If you want the value of Fiscal Month + Fiscal Date, where the Fiscal Month field counts the number of individuals, then you can refer to a formula similar to the following.

``````Column =
COUNTROWS ( 'Table' )
+ CALCULATE (
ALLEXCEPT ( 'Table', 'Table'[Fiscal Date].[Month] )
)``````

If the problem is still not resolved, can you provide the relevant test data (remove sensitive information) to facilitate me to answer for you as soon as possible. Looking forward to your feedback.

Best Regards,
Henry

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

3 REPLIES 3
Community Support

Hi  @Andrea_Jess ,

According to your description, the problem may be caused by the wrong statistics of the formula. If you want the value of Fiscal Month + Fiscal Date, where the Fiscal Month field counts the number of individuals, then you can refer to a formula similar to the following.

``````Column =
COUNTROWS ( 'Table' )
+ CALCULATE (
ALLEXCEPT ( 'Table', 'Table'[Fiscal Date].[Month] )
)``````

If the problem is still not resolved, can you provide the relevant test data (remove sensitive information) to facilitate me to answer for you as soon as possible. Looking forward to your feedback.

Best Regards,
Henry

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

Impactful Individual

One way to accomplish this is to create a calculated column and a measure that uses it.

Same date = IF( 'Table'[date1] = 'Table'[date2], TRUE(), FALSE() )

Sum =
CALCULATE( SUM('Table'[value] ), 'Table'[Same date] = TRUE() )

This measure only takes sum of values where date1 equals to date2.

Hope this helps!

Community Champion

Can you please explain what data you are getting?

And it would be nice if you can please share the desired output from your data.

Proud to be a Super User!