Forum Discussion

GlynMThomas's avatar
GlynMThomas
Resolver I
5 years ago
Solved

Sum By Column

Hi,

 

I have a table like below:

 

NameGroupMonthAmount
BobTennisJan100
BobFootballJan50
FredTennisJan50
MaryFootballFeb40
MaryGolfJan300

 

I need to sum each by name for each month, then flag them as paying over or under £100 for each month so I can then use this new flag/column in a filter (the group is irrelevant but this is how the table is aggregated as it's used for something else). Is there an easy way to do this? I was looking at a summarised table but thought it seems a bit overkill.

Need Help
  • AlB's avatar
    AlB
    5 years ago

    GlynMThomas 

    You can use the measure to filter a visual. Im not sure I understand what you need. If it's a calculated column in the table above, then:

    Flag =
VAR amountCustomerMonth_ =
    CALCULATE (
        SUM ( Table1[Amount] ),
        ALLEXCEPT ( Table1, Table1[Name], Table1[Month] )
    )
RETURN
    IF (
        amountCustomerMonth_ > 100,
        "Paying over 100 quid",
        "NOT paying over 100 quid"
    )

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

7 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    GlynMThomas 

    You can use the measure to filter a visual. Im not sure I understand what you need. If it's a calculated column in the table above, then:

    Flag =
VAR amountCustomerMonth_ =
    CALCULATE (
        SUM ( Table1[Amount] ),
        ALLEXCEPT ( Table1, Table1[Name], Table1[Month] )
    )
RETURN
    IF (
        amountCustomerMonth_ > 100,
        "Paying over 100 quid",
        "NOT paying over 100 quid"
    )

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi GlynMThomas 

    1. Place Name and Month in a table visual

    2. Create this measure and place it in the visual

    Flag =
IF ( SUM ( Table1[Amount] ) > 100, "Paying over 100 quid" )

    This will show only the rows meeting the condition

    You can also do this through a calculated table

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

    • GlynMThomas's avatar
      GlynMThomas
      Resolver I
      5 years ago

      Thanks, I've already done this column but it doesn't take the fact the value is split by the Group column as well. I want it to sum by the month for each person and work out if they paid over 100 in that month. For example, Bob paid £150 in Jan so should be flagged as paying over £100, however this wouldn't flag him as over £100 as it seeing the rows as £50 and £100.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    GlynMThomas , create these two new columns and use the flag as a filter

     

    New column =
    var _1 = sumx(filter(Table, [Name] =earlier[Name] && [Month] =earlier[Month]),[Amount])
    return if(_1>100, 1, 0)

    Flag =
    var _1 = countx(filter(Table, [Name] =earlier[Name]),[New column])
    var _2 = sumx(filter(Table, [Name] =earlier[Name]),[New column])
    return
    if(_1 =_2, 1,0)

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    GlynMThomas 

    I'm not sure who you're replying to. The solution above works as intended. See it all at work in the attached file.

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • GlynMThomas's avatar
      GlynMThomas
      Resolver I
      5 years ago

      AlB 

       

      It does for a measure, but I need to apply that to to a filter so I can filter between over £100 and under £100. I can't put a measure in a filter.

       

      Many Thanks