Sum By Column

[GlynMThomas]

Hi,

I have a table like below:

Name	Group	Month	Amount
Bob	Tennis	Jan	100
Bob	Football	Jan	50
Fred	Tennis	Jan	50
Mary	Football	Feb	40
Mary	Golf	Jan	300

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.

[AlB · Accepted solution]

@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 

[GlynMThomas]

@AlB 

Yeah that did it. Many thanks for your help.

[AlB]

@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]

@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

[amitchandak]

@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]

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]

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.