Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate % change from previous row based on multiple columns

Good day, 

I need to show analysis (% change of Total Viewers per Episode) for different packages (Gold, Silver, Bronze). The Title and Station of the Episode doesn't matter. My sample data is below and expected output (% change per Episode) is provided thereafter.

 

*Note that the number of Titles / which Package they get aired on varies by Episode. 

 

I have been able to obtain a clunky solution by: duplicating the table, filtering on one package, group by Episode number, adding an index, creating a calculated column referencing the previous Episode (by Index), and then calculating the % change per episode. The disadvantage is that I would need to do this for all packages (my real data set has 7 packages), and then create separate visualisations for each table, so no user interactivity. I was hoping there's a more elegant solution? 

 

Sample data:

 

TitlePackageEpisode numberStationViewers
ProgrammeGold101A30
HighlightsGold101A25
ProgrammeSilver101A50
HighlightsSilver101A70
HighlightsBronze101A40
ProgrammeGold101B15
HighlightsGold101B10
ProgrammeSilver101B40
HighlightsSilver101B30
HighlightsBronze101B20
ProgrammeGold102A20
HighlightsGold102A40
ProgrammeSilver102A60
HighlightsSilver102A55
HighlightsBronze102A65
ProgrammeGold101B10
HighlightsGold101B20
ProgrammeSilver101B25
HighlightsBronze101B15
ProgrammeGold103A20
HighlightsGold103A15
ProgrammeSilver103A45
HighlightsBronze103A15

 

 

 

 

 

Expected output (% change in Viewers per Episode):

 

Thank you in advance for your time and expertise. 

 

 

 

 

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Anonymous ,

     

    First create a  Episode table and dont create a relationship between;

    Then create a measure as below:

    Measure =
VAR _previous =
    CALCULATE (
        MAX ( 'Table'[Episode number] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Episode number] < MAX ( 'Table'[Episode number] )
        )
    )
VAR _previoussum =
    CALCULATE (
        SUM ( 'Table'[Viewers] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Episode number] = _previous
                && 'Table'[Package] IN FILTERS ( Slicer[Package] )
        )
    )
VAR _currentsum =
    CALCULATE (
        SUM ( 'Table'[Viewers] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Episode number] = MAX ( 'Table'[Episode number] )
                && 'Table'[Package] IN FILTERS ( Slicer[Package] )
        )
    )
RETURN
    IF (
        _previous = BLANK (),
        BLANK (),
        DIVIDE ( _currentsum - _previoussum, _previoussum )
    )

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you v-kelly-msft 

    In the meantime, my colleague came up with another solution that allows both the Viewers and % Change to be updated from the same slicer:  

     

    WoW % Change =
    VAR CurrentEpisode = SELECTEDVALUE('Table'[Episode number])
    VAR PreviousEpisode = CurrentEpisode -1
    VAR CurrentEpisodeScore = SUM('Table'[Viewers])
    VAR PreviousEpisodeScore = CALCULATE( SUM('Table'[Viewers]) , 'Table'[Episode number] = PreviousEpisode)
    VAR Wow = DIVIDE( CurrentEpisodeScore - PreviousEpisodeScore , PreviousEpisodeScore )
    RETURN
    Wow
     
    This solution also allows additional analysis by Title or Station, if required (see comparison between the two measures below):
     

    Thank you again for your input.

     

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi Anonymous ，

     

    Good solution,but one thing needs to be reminded,if the previous Episode number not equals the current one minus 1,then the solution will return error,you could check my solution which will avoid such error.

    If you feel my solution is also a good one,I hope you could also mark my reply as answered to let more people find it.

    Thanks in advance.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , if you need just based on episode. Better to create a table with distinct Episode and join it with Episode of this table

     

    then

    measure =

    var _1 = sum(Table[Viewer])

    var _2 = calculate(_1, filter(all(Episode), Episode [Episode] = maX(Episode[Episode])-1))

    return

    divide(_2,_1)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you amitchandak 

      I'm not sure what I'm doing wrong, I created an Episode table (Distinct), linked to my data, and the measure, but the measure shows 100% regardless of the filter context. 

       

      The measure:

       

      % Change =
      var _1 = sum('Table'[Viewers])
      var _2 = calculate(_1, filter(all(Episode), Episode[Episode number] = MAX(Episode[Episode number])-1))
      return
      divide(_2,_1)

       

      My Data model:

      Thank you again for your time. 

       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        4 years ago

        Hi  Anonymous ,

         

        First create a  Episode table and dont create a relationship between;

        Then create a measure as below:

        Measure =
VAR _previous =
    CALCULATE (
        MAX ( 'Table'[Episode number] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Episode number] < MAX ( 'Table'[Episode number] )
        )
    )
VAR _previoussum =
    CALCULATE (
        SUM ( 'Table'[Viewers] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Episode number] = _previous
                && 'Table'[Package] IN FILTERS ( Slicer[Package] )
        )
    )
VAR _currentsum =
    CALCULATE (
        SUM ( 'Table'[Viewers] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Episode number] = MAX ( 'Table'[Episode number] )
                && 'Table'[Package] IN FILTERS ( Slicer[Package] )
        )
    )
RETURN
    IF (
        _previous = BLANK (),
        BLANK (),
        DIVIDE ( _currentsum - _previoussum, _previoussum )
    )

        And you will see:

        For the related .pbix file,pls see attached.

         

        Best Regards,
        Kelly

        Did I answer your question? Mark my reply as a solution!