Forum Discussion

Berrcikk's avatar
Berrcikk
Frequent Visitor
3 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
Icon for Super User rankSuper User
3 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
3 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
    3 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
      3 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
        Icon for Super User rankSuper User
        3 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