Forum Discussion

anithaMallam's avatar
anithaMallam
Helper III
5 years ago
Solved

Indicators in Score Card

Hi All,

           I need your help in showing the comparision of sales to current and last year sales.If last year  sales is lower than current year in the score card below total sales value by what percentage last year sales is lower it should be displayed with an indicator with down arrow and if previous year sales is greater than current year it has to show up arrow below the total sales values in score card.Could some one please help me on this.

 

Regards

Anitha

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi anithaMallam 

    Due to I don‘t know your data model, I build a sample table with Date column and Sales column to have a test.

    Measure:

    Icon = 
VAR _Current =
    SUMX (
        FILTER ( 'Table', 'Table'[Date].[Year] = YEAR ( TODAY () ) ),
        'Table'[Sales]
    )
VAR _Previous =
    SUMX (
        FILTER ( 'Table', 'Table'[Date].[Year] = YEAR ( TODAY () ) - 1 ),
        'Table'[Sales]
    )
RETURN
    IF ( _Previous < _Current, "⇩", "⇧" )

    And you can use UNICHAR(9650)/ UNICHAR(9660) in Measure as well.

    Sales in Lastyear is greater than current year, so measure will return to up arrow in your logic. Result is as below.

    You can download the pbix file from this link: Indicators in Score Card

     

    Best Regards,

    Rico Zhou

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    anithaMallam , For up and down arrow, You can refer this solution from past. Using UNICHAR and color measure.

    https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

     

    For this year vs last year. With the date table, you can use

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    //Only year vs Year, not a level below

     


    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Also refer : https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

    Appreciate your Kudos.

  • anithaMallam's avatar
    anithaMallam
    Helper III
    5 years ago

    Hi Amit,

                   

    Icon = 
VAR theIndicator = [Parameter Value]
RETURN
   SWITCH (
            TRUE(),
            theIndicator = 1, UNICHAR(9650),
            theIndicator = 0, UNICHAR(9654),
            theIndicator = 2, UNICHAR(9660)
            )

    in the place of [ Parameter value  ] in the above logic can i take %diff of current and last year sales value.

     

    Regards

    Anitha

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi anithaMallam 

      Due to I don‘t know your data model, I build a sample table with Date column and Sales column to have a test.

      Measure:

      Icon = 
VAR _Current =
    SUMX (
        FILTER ( 'Table', 'Table'[Date].[Year] = YEAR ( TODAY () ) ),
        'Table'[Sales]
    )
VAR _Previous =
    SUMX (
        FILTER ( 'Table', 'Table'[Date].[Year] = YEAR ( TODAY () ) - 1 ),
        'Table'[Sales]
    )
RETURN
    IF ( _Previous < _Current, "⇩", "⇧" )

      And you can use UNICHAR(9650)/ UNICHAR(9660) in Measure as well.

      Sales in Lastyear is greater than current year, so measure will return to up arrow in your logic. Result is as below.

      You can download the pbix file from this link: Indicators in Score Card

       

      Best Regards,

      Rico Zhou

       

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi anithaMallam 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou