Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

% Change over user selected year columns

Hi all,

 

First time poster! I'd appreciate any help you can give me with this question...

 

I need to create a dynamic matrix and line chart that shows the percentage change over selected year columns. Example below.

 

There are 10 years in the report and a user can select any combination of years. The selected years need not be contiguous, but are displayed in order (L to R). If a user adds more years, the table recalculates to reflect all possible % changes. If only 1 year is selected you'd see a blank table/chart. I think this means that a date table may not be appropriate as I want the calculation to work over non-contiguous years.

 

The result I'm looking for is exactly the same as if you used a pivot table to show % Difference From (previous).

 

So from this:

 

Category202020222023
A405379338089411576
B263250229064279059
C198641103715044
D9030743171125443
E305931760029115
Grand Total809393638961860237

 

I want this:

 

Category202020222023
A -17%22%
B -13%22%
C -44%36%
D -52%191%
E -42%65%
Grand Total -21%35%

 

And this:

 

 

 

Happy to provide more info.

 

Many thanks!

 

Chris.

Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    Hi Anonymous 

    Maybe try something like this.

     

    Pct Chg = 
VAR _CurrentSelected = [Total Value]
VAR _PreviousSelected =
    CALCULATE(
        [Total Value],
        OFFSET(
            -1,
            ALLSELECTED( 'Table'[Year] ),
            ORDERBY( 'Table'[Year] )
        )
    )
VAR _Logic =
    DIVIDE(
        _CurrentSelected - _PreviousSelected,
        _PreviousSelected
    )
RETURN
    _Logic

     

     

6 Replies

  • CoreyP's avatar
    CoreyP
    Icon for Solution Sage rankSolution Sage
    2 years ago

    I'm not gonna lie... this took me absolutely wayyy longer than it probably should have. But, it was a bit tricky. You do need a date dimension table. IMHO, if a date exists anywhere in your model, you should always have a date dimension table in Power BI. 

     

    Delta = 
VAR _currenttotal = [Total Value]
VAR _previoustotal = 
CALCULATE( 
    [Total Value] , 
        'Date'[Date].[Year] = 
            MAXX( 
                FILTER( 
                    CALCULATETABLE( 
                        VALUES( 'Date'[Date].[Year] ) , 
                        ALLSELECTED('Date'[Date].[Year] ) 
                                ) ,
                        'Date'[Date].[Year] < MAX( 'Date'[Date].[Year] ) 
                        ) , 
                'Date'[Date].[Year] 
                )
            )
VAR _delta = _currenttotal - _previoustotal
VAR _pct = DIVIDE( _delta , _previoustotal , BLANK() )

RETURN
_pct
  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 

    Maybe try something like this.

     

    Pct Chg = 
VAR _CurrentSelected = [Total Value]
VAR _PreviousSelected =
    CALCULATE(
        [Total Value],
        OFFSET(
            -1,
            ALLSELECTED( 'Table'[Year] ),
            ORDERBY( 'Table'[Year] )
        )
    )
VAR _Logic =
    DIVIDE(
        _CurrentSelected - _PreviousSelected,
        _PreviousSelected
    )
RETURN
    _Logic

     

     

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      2 years ago

      This is a good use of OFFSET. Short, easy to understand and fast.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Perfect! Thank you! Highly appreciated! 😎

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi there - that solution is working super well. However there's one enhancement I'd like if possible - can the earliest year also be shown as a blank column? This is necessary because having the ability to select any combination of years means that the earliest year might not be the year previous to the earliest one displayed. I hope that makes sense!
      Thank you.

      • gmsamborn's avatar
        gmsamborn
        Icon for Super User rankSuper User
        2 years ago

        Hi Anonymous 

         

        I'm not really sure what you mean.  Can you show me an example?