Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Category | 2020 | 2022 | 2023 |
A | 405379 | 338089 | 411576 |
B | 263250 | 229064 | 279059 |
C | 19864 | 11037 | 15044 |
D | 90307 | 43171 | 125443 |
E | 30593 | 17600 | 29115 |
Grand Total | 809393 | 638961 | 860237 |
I want this:
Category | 2020 | 2022 | 2023 |
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.
Solved! Go to Solution.
Hi @ChrisIrvin
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
Hi @ChrisIrvin
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
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.
Perfect! Thank you! Highly appreciated! 😎
This is a good use of OFFSET. Short, easy to understand and fast.
Proud to be a Super User!
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |