Trend indicator in matrix ccomparing years

[Anonymous]

Hello everyone,

I would like to create a matrix visual that looks like the following table:

I struggle to create the trend indicator. The arrows should indicate whether the number of sold items in one year increased or decreased compared to the last year. Also, there should be an overall arrow indicating whether the number of sold items increased or decreased per item over all years.

Thank you so much in advance!

[Anonymous]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Column =
var _2019=SUMX(FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item])&&'Table'[Year]=2019),[No.of sold items])
var _all=SUMX(FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item])&&'Table'[Year]=EARLIER('Table'[Year])),'Table'[No.of sold items])
return
SWITCH(
    TRUE(),
    _all>_2019,UNICHAR ( 9650 ),
    _all=_2019,BLANK(),
    _all<_2019,UNICHAR ( 128315 ))

2. There seems to be no green arrow in unichar, so we choose green for Format-Field formatting-Font color.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Jihwan_Kim · Accepted solution]

Trend indicator : =
VAR _startyear =
CALCULATE ( MIN ( 'Calendar'[Year] ), REMOVEFILTERS ( 'Calendar' ) )
VAR _latestyear =
CALCULATE ( MAX ( 'Calendar'[Year] ), REMOVEFILTERS ( 'Calendar' ) )
VAR _currentyear =
MAX ( 'Calendar'[Year] )
VAR _previousyear = _currentyear - 1
VAR _currentyearqty = [Sold Items Total :]
VAR _previousyearqty =
CALCULATE ( [Sold Items Total :], 'Calendar'[Year] = _previousyear )
VAR _comparetoPreviousYear =
IF (
NOT ISBLANK ( _previousyearqty ),
IF ( _currentyearqty >= _previousyearqty, "🔼", "🔽" )
)
VAR _startyearqty =
CALCULATE ( [Sold Items Total :], 'Calendar'[Year] = _startyear )
VAR _latestyearqty =
CALCULATE ( [Sold Items Total :], 'Calendar'[Year] = _latestyear )
VAR _overalltrend =
IF ( _latestyearqty >= _startyearqty, "🔼", "🔽" )
RETURN
IF (
HASONEVALUE ( Items[Item] ),
SWITCH (
TRUE (),
ISINSCOPE ( 'Calendar'[Year] ), _comparetoPreviousYear,
_overalltrend
)
)

Link to the sample pbix file