Forum Discussion

Berrcikk's avatar
Berrcikk
Frequent Visitor
2 years ago
Solved

KPI Visual - target value

Hi, I’m having a problem with creating a simple report with one chart and one target visual. Let’s assume that in my model, I have fact table with total sales,product column and dim calendar. What ...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Berrcikk 
    You can use isinscope function to manipulate with the date hierarchies and calculate your goal according to the selections.
    Please refer to the attached video :
    https://www.youtube.com/watch?v=_p9E8fiDRDw

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

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Berrcikk 
    INSCOPE / Isfiltered will work, as the "goal" of the card filtered by bars on the graph.

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Berrcikk 
    You need to apply the needed logic for goals based on the hierarchy filter, if i understood you correctly, on year it should be a year ago, quarter, quarter ago, etc . 
    So it something like :
    test isincscope =
    VAR date1 =
        MAX ( DimCalendar[Date] )
    VAR vMonth =
        CALCULATE (
            SUM ( vFactSales[Total Sales] ),
             datedadd( vFactSales[Date] ,-1,month)
        )
    VAR vQuarter =
        CALCULATE (
            SUM ( vFactSales[Total Sales] ),
            datedadd( vFactSales[Date] ,-1,quarter)
        )
    VAR vYear =
        CALCULATE (
            SUM ( vFactSales[Total Sales] ),
            datedadd ( vFactSales[Date] ,-1,Year)
        )
    VAR scope1 =
        SWITCH (
            TRUE (),
            ISINSCOPE ( DimCalendar[Date].[Month] ), vMonth,
            ISINSCOPE ( DimCalendar[Date].[Month] ), vQuarter,
            ISINSCOPE ( DimCalendar[Date].[Month] ), vYear,
            1
        )
    RETURN
        scope1

Berrcikk's avatar
Berrcikk
Frequent Visitor
2 years ago

Ritaf1983 Sure, I hope this format will be fine.

So fact table looks more or less like that.

sales valuedatecar type
1004.10.2020A
2519.09.2020A
3217.08.2020B
1414.06.2020B

 

Now, let's assume we have a slicer date, and a clustered chart with sales on Y axis, and date on X axis.

Based on what granularity on chart is shown (ex. years, months, quarters), KPI card should display value for current month, and target for previous one. So for screen below, it should be 548 and 535.

However, if I change granularity to quarters, as on the screen below, it should present 1.3k, and 1k.

 

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
2 years ago

Hi Berrcikk 
You can use isinscope function to manipulate with the date hierarchies and calculate your goal according to the selections.
Please refer to the attached video :
https://www.youtube.com/watch?v=_p9E8fiDRDw

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

  • Berrcikk's avatar
    Berrcikk
    Frequent Visitor
    2 years ago

    Hi Ritaf1983 
    I believe INSCOPE function is not what I'm looking for, as date hierarchy is determined in separate visual, so therefore I think it doesn't work in this scenario 

    • Ritaf1983's avatar
      Ritaf1983
      Icon for Super User rankSuper User
      2 years ago

      Hi Berrcikk 
      INSCOPE / Isfiltered will work, as the "goal" of the card filtered by bars on the graph.

      • Berrcikk's avatar
        Berrcikk
        Frequent Visitor
        2 years ago

        I'm not sure If I understand you correctly, what you say definetely makes sense, but I have a feeling I don't catch it... For me it looks like, that changing data granularity on chart, doesn't effect in any way my other visuals. I'm very grateful, but could you maybe share with me working example of such a feature? Below is my code for calculating current goal, that I would like to react to changes in granularity in other chart


        test isincscope =
        VAR date1 =
            MAX ( DimCalendar[Date] )
        VAR vMonth =
            CALCULATE (
                SUM ( vFactSales[Total Sales] ),
                MONTH ( date1 ) = MONTH ( vFactSales[Date] )
            )
        VAR vQuarter =
            CALCULATE (
                SUM ( vFactSales[Total Sales] ),
                QUARTER ( date1 ) = QUARTER ( vFactSales[Date] )
            )
        VAR vYear =
            CALCULATE (
                SUM ( vFactSales[Total Sales] ),
                YEAR ( date1 ) = YEAR ( vFactSales[Date] )
            )
        VAR scope1 =
            SWITCH (
                TRUE (),
                ISINSCOPE ( DimCalendar[Date].[Month] ), vMonth,
                ISINSCOPE ( DimCalendar[Date].[Month] ), vQuarter,
                ISINSCOPE ( DimCalendar[Date].[Month] ), vYear,
                1
            )
        RETURN
            scope1

        And a screen of how other visual doesn't react...

         


        Thanks for your patience!