Incorrect Sum of Rows

[Anonymous]

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? 

Thanks in advance

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

[v-henryk-mstf · Accepted solution]

Hi  @Anonymous ,

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 (
        SUM ( 'Table'[Headcount] ),
        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.

[YukiK]

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!

[FarhanAhmed]

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.