Unable to display trend arrows in table visual.

[vijethshetty22]

Hello,

I am looking for options to display trend image (Up or Low Arrows) for my sales values w.r.t MOM (month on month) changes.

I had gone through http://community.powerbi.com/t5/Desktop/up-arrow-show-a-shape-based-on-a-formula/td-p/15823 but still could not find solution for my issue.

Below is the complete description of task I am trying to perform.
I am using two excel files as input. Below is the content of 2 excel.
1. Sales
---------------
Date Sales
Apr-16 809
May-16 719
Jun-16 698
Jul-16 788
Aug-16 749
Sep-16 902
Oct-16 1017
Nov-16 1138
Dec-16 1371
Jan-17 1368
Feb-17 1254
Mar-17 937

2. Key Excel
------------
Key Value
1 https://thumb.ibb.co/ns9zQa/Up_Arrow.png
2 https://thumb.ibb.co/ePvTJv/down_arrow.png

In 2nd excel the URL points to the image to be displayed.

Used below 3 measures to display the % Change in sales.

Total Sales = SUM(Sales[Sales])
Previous Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH(Sales[Date]))
% Change Sales = DIVIDE([Total Sales],[Previous Month Sales],BLANK())-1

To display the arrow key for the trend i have used multiple approaches (measure and Custom column) but none is working. Below is the details of measures/columns.

First 2 are measures. Last one is custom column.

1. Sales Key = IF([% Change Sales]>0,"⇧", "⇩")
Comment - This works but i would like to have images rather than the ASCII arrows.

2. Sales Key 2 = IF([% Change Sales]>0,"https://thumb.ibb.co/ns9zQa/Up_Arrow.png", "https://thumb.ibb.co/ePvTJv/down_arrow.png")
Comment - This is displaying the URL's correctly. But as Data Category of a measure cannot be changed (to Image URL), I am unable to get the image out of it.

3. Sales Key Column = IF([% Change Sales]>0,"https://thumb.ibb.co/ns9zQa/Up_Arrow.png", "https://thumb.ibb.co/ePvTJv/down_arrow.png")
Comment - By changing the data category, this displays the image but irrespective of '% Change Sales' of individual months, it always displays Down Arrow.

I have placed all the above measures in a Table visual. Below is the screen shot of the same.

Thanks in advance for you help !!

[v-shex-msft · Accepted solution]

Hi @vijethshetty22,

Since measure no support to modify the data category, if you want to show the image, you have to use calculate column.

According to my research, your measure contians some dax formula which based on the filter effect of visual. Calculate column is an pretreatment column, if you directly use above formulas in calculate column, it will caused the issue.

To work through this issue, you can take a look at below calculated column formulas:

Total Sales 2 = SUMX(FILTER(ALL(Sales),[Date]=EARLIER(Sales[Date])),Sales[Sales])

Previous Month Sales 2 = SUMX(FILTER(ALL(Sales),[Date].[MonthNo]=EARLIER(Sales[Date].[MonthNo])-1),Sales[Sales])

% Change Sales 2 = DIVIDE([Total Sales 2],[Previous Month Sales 2],BLANK())-1

Sales Key 2 = 
IF([% Change Sales 2]>0,"https://thumb.ibb.co/ns9zQa/Up_Arrow.png", "https://thumb.ibb.co/ePvTJv/down_arrow.png")

Regards,

Xiaoxin Sheng

[vijethshetty22]

Hi @v-shex-msft,

Thanks a lot. This helps. Solved my problem.