Calculate the sum of values in a column based on two other filtered columns

[xucha]

Hi all,

I've been stuck on this formula for too long now, when I feel it shouldn't be that hard?

Here's my issue: I'd like to find the totals of the values in column A, for all MAX and MIN values in column B over every distinct value of column C. To be clearer, I want to know the totals of viewers for the first and last episodes of each season, across all countries.

Here's an excerpt of my table:

country	episode	season	viewers
fr	1	s1	10000
uk	1	s1	14000
us	1	s1	20000
fr	2	s1	9050
uk	2	s1	13000
us	2	s1	17500
fr	3	s1	9020
uk	3	s1	12800
us	3	s1	15050
fr	4	s2	9050
uk	4	s2	13000
us	4	s2	17050
fr	5	s2	8000
uk	5	s2	12000
us	5	s2	15500
fr	6	s2	8100
uk	6	s2	11900
us	6	s2	15700

and here's the same table with my custom columns:

country	episode	season	viewers	starters	finishers
fr	1	s1	10000	44000	36870
uk	1	s1	14000	44000	36870
us	1	s1	20000	44000	36870
fr	2	s1	9050	44000	36870
uk	2	s1	13000	44000	36870
us	2	s1	17500	44000	36870
fr	3	s1	9020	44000	36870
uk	3	s1	12800	44000	36870
us	3	s1	15050	44000	36870
fr	4	s2	9050	38100	35700
uk	4	s2	13000	38100	35700
us	4	s2	17050	38100	35700
fr	5	s2	8000	38100	35700
uk	5	s2	12000	38100	35700
us	5	s2	15500	38100	35700
fr	6	s2	8100	38100	35700
uk	6	s2	11900	38100	35700
us	6	s2	15700	38100	35700

I've tried many formulas (sorry can't copy/paste here, PBI'S not on my personal computer) including functions such as ALLEXCEPT() and FILTER() but so far, didn’t find the right one…

A little help here please?

Thank you!!

[MartynRamsden · Accepted solution]

Hi @xucha 

Add these as calculated columns:

Starters = 
VAR RowSeason = Table1[season]
VAR MinEpisode =
CALCULATE (
    MIN ( Table1[episode] ),
    FILTER (
        ALL ( Table1 ),
        Table1[season] = RowSeason
    )
)
VAR Result = 
CALCULATE ( 
    SUM ( Table1[viewers] ),
    FILTER (
        ALL ( Table1 ),
        Table1[season] = RowSeason
        && Table1[episode] = MinEpisode
    )
)
RETURN Result

Finishers = 
VAR RowSeason = Table1[season]
VAR MaxEpisode =
CALCULATE (
    MAX ( Table1[episode] ),
    FILTER (
        ALL ( Table1 ),
        Table1[season] = RowSeason
    )
)
VAR Result = 
CALCULATE ( 
    SUM ( Table1[viewers] ),
    FILTER (
        ALL ( Table1 ),
        Table1[season] = RowSeason
        && Table1[episode] = MaxEpisode
    )
)
RETURN Result

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[sturlaws]

if you want it as measures instead try this:

MeasureFinishers =
VAR _last =
    CALCULATE ( MAX ( 'Table'[episode] ); ALL ( 'Table' ) )
RETURN
    SUMX (
        VALUES ( 'Table'[country] );
        VAR _country =
            CALCULATE ( SELECTEDVALUE ( 'Table'[country] ) )
        RETURN
            CALCULATE (
                SUM ( 'Table'[viewers] );
                FILTER ( 'Table'; 'Table'[episode] = _last && 'Table'[country] = _country )
            )
    )

[sturlaws]

Hi, it seems like you are trying to create calculated columns. If that is the case you can write dax code like this:

starters =
VAR _country =
    CALCULATE ( SELECTEDVALUE ( 'Table'[country] ) )
VAR _firstEpisode =
    CALCULATE ( MIN ( 'Table'[episode] ); ALL ( 'Table' ) )
RETURN
    CALCULATE (
        VALUES ( 'Table'[viewers] );
        FILTER (
            ALL ( 'Table' );
            'Table'[country] = _country
                && 'Table'[episode] = _firstEpisode
        )
    )

and 

finishers =
VAR _country =
    CALCULATE ( SELECTEDVALUE ( 'Table'[country] ) )
VAR _lastEpisode =
    CALCULATE ( MAX ( 'Table'[episode] ); ALL ( 'Table' ) )
RETURN
    CALCULATE (
        VALUES ( 'Table'[viewers] );
        FILTER (
            ALL ( 'Table' );
            'Table'[country] = _country
                && 'Table'[episode] = _lastEpisode
        )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.