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

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

Reply
Andrea_Jess
Helper III
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? 

 

Thanks in advance

 

Fiscal MonthFiscal DateHeadcountSum of Headcount

Aug-21

Jul-21515
Aug-21Aug-21520
Aug-21Sept-21524
Aug-21Oct-21524
Aug-21Nov-21530
Aug-21Dec-21435
Aug-21

Jan-22

365
Aug-21

Feb-22

567
Aug-21

Mar-22

488
Aug-21

Apr-22

389
Aug-21

May-22

490
Aug-21

Jun-22

395
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
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 (
        SUM ( 'Table'[Headcount] ),
        ALLEXCEPT ( 'Table', 'Table'[Fiscal Date].[Month] )
    )

vhenrykmstf_0-1636513624307.png

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.

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
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 (
        SUM ( 'Table'[Headcount] ),
        ALLEXCEPT ( 'Table', 'Table'[Fiscal Date].[Month] )
    )

vhenrykmstf_0-1636513624307.png

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
Impactful Individual
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.

 

YukiK_0-1636352285546.png

 

 

Hope this helps!

FarhanAhmed
Community Champion
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.

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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