Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Trend indicator in matrix ccomparing years

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 ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    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 

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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