Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Adding icons to table visual based on field value

Hi,

 

I have a table (See the pictures) where I have Period, KPI, and a value for that KPI of the KPI. I have created a table visual where there is Actual, Target, and Status. In order to calculate Actual, I used YTD average for Cost and Precision, and took the value of the last month for Revenue. I have also predefined targets stored in Target measure. Then I have created Status measure. If Actual is bigger than Target it shows upside triangle, and vice vera if Actual is less than Target. I can add Status (icons) in a seperate column. But I want to take icons next to Actual to the same column. Is there any way I can do that? I tried it through conditional formattting but could not do that.

 

Any help is appreciated!

 

Thanks in advance!

 

The measures: 

Actual = 
var Cost_ = CALCULATE(AVERAGE('Table'[Value]),DATESYTD('Table'[ Period]),FILTER('Table','Table'[KPI]="Cost"))
var Revenue_ = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[ Period]=MAX('Table'[ Period])),'Table'[KPI]="Revenue")
var Precision_ = CALCULATE(AVERAGE('Table'[Value]),DATESYTD('Table'[ Period]),FILTER('Table','Table'[KPI]="Precision"))

Return SWITCH(MAX('Table'[KPI]),
"Revenue",Revenue_,"Cost", Cost_,"Precision",Precision_)
Target = 
var precision = 0.99
var cost = 50
var revenue = 300
RETURN  
SWITCH(MAX('Table'[KPI]),"Cost",cost,"Revenue",revenue, "Precision", precision)
Status = SWITCH(TRUE(),[Actual]>[Target], UNICHAR(9650),UNICHAR(9660))

 

Data

 

Table visiual

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    If you want it in the same column you would have to do concatenation and use UNICHAR function.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Greg_Deckler ,

       

      Thanks for the reply. What do you mean by saying that I should do concatenation. Can you please give an example?

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion

        Anonymous  - Sure, 

         

        Measure = [Target] & " " & UNICHAR(9733)

         

        Concatenation.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi v-diye-msft

       

      Thanks for the links. Now I realize that I can do that by using SVG code. But I still wonder why it does not work when I add my "Status" measure (icons) through conditioanal formatting field value. I just get a space next to the data not the icon itself. I also choose the layout. 

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion

        Anonymous  - I'm confused over the current ask here. It seemed like you had conditional formatting and icons working in the original post in a separate column. The method I proposed with UNICHAR, you would add the logic of which color icon to display to that code so you would choose whatever color icon you wanted as your UNICHAR, like:

         

        VAR __char = IF([Sum] > 0,UNICHAR(1),UNICHAR(2))

        RETURN

        [Sum] & " " & __char

         

        If you need SVG icons, I created a bunch of them here: https://community.powerbi.com/t5/Quick-Measures-Gallery/SVG-Multi-Shape-Multi-Color-Indicators/m-p/500486#M199

         

        But, I've become confused over what you are trying to accomplish.