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 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!

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 

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    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