Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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.
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.
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 )
)
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |