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

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

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      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