Forum Discussion

GlynMThomas's avatar
GlynMThomas
Resolver I
5 years ago
Solved

Sum By Column

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

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.