KPI Visual - target value

[Berrcikk]

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 I would like to achieve, is to calculate target on KPI visual card, as a a value of total sales for previous period on visual. So if on chart there are years, KPI card should display value for newest year, and target as total for previous year. Same, if I’m presenting quarters, then It should calculate value for current quarter, and target value as value for previous quarter. Also, it would be great to have an exception, that if there is no value for previous period, then target is not calculated. As for now, I’ve hard coded it for months, but I believe there is easier solution that I’m not aware of.

[Ritaf1983 · Accepted solution]

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 · Accepted solution]

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

[Berrcikk]

@Ritaf1983 Sure, I hope this format will be fine.

So fact table looks more or less like that.

sales value	date	car type
10	04.10.2020	A
25	19.09.2020	A
32	17.08.2020	B
14	14.06.2020	B

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 · Accepted solution]

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]

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 · Accepted solution]

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

[Berrcikk]

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 · Accepted solution]

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

[Ritaf1983]

Hi @Berrcikk 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523