Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Change KPI target in report view

I work with a non-profit and we have a visualization showing donations by day over a fiscal year. This donation receipts are non-linear over the year. We tend to receive a lot during the winter months, but things are much slower in the spring and summer.

 

I have a KPI that tracks the fiscal year to date trend compared with the previous year. This works well. But, I'd like to be able to have a slider/text box in the report view where a user could pick a target amount for the fiscal year. Let's say that amount is 5 million by the end of the year. Then, Power BI would show a KPI based on the historical distribution of our donations but ending in 5 million at the conclusion of the fiscal year. It would show how we're tracking against a normal path to reach 5 million.

 

I think I know how to get the distribution worked out. I could basically just calculate the year-to-date percentage of all contributions historically. With those, I could multiply the percentages by the final value to get the daily targets. But, is there any kind of visualization that would let a user change that 5 million number? It'd be awesome if I could make a slider, for example, that would let them move it between 3 million and 7 million, for instance.

 

Is that possible?

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi Anonymous,

     

    I think it's hard to do it directly after testing a lot. Maybe there is a workaround. As you said in your post, you track the KPI to date. So you could have spread the 5 million to the whole year by days. Let's start here.

    1. In my sample, I compare the sales quantity of the year 2008 to the year 2007. 

    2. Create a table named "KPItimes" like this.

    Times

    0.6
    0.7
    0.8
    0.9
    1
    1.1
    1.2
    1.3
    1.4

    3. Measure to sum the quantities.

    SalesQuantitySum =
SUM ( 'Sales'[Quantity] )

    4. Measure to sum the quantities of last year. (To you, it's 5 million. This step is the key point of this workaround)

    SalesQuantityLastyear =
IF (
    HASONEVALUE ( 'KPItimes'[Times] ),
    MIN ( KPItimes[Times] )
        * CALCULATE ( SUM ( Sales[Quantity] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
    CALCULATE ( SUM ( Sales[Quantity] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

    5. Create KPI visual and a slicer of 'KPItimes'[Times]. Finally, we can change the value of the slicer to change the goal from 3 million to 7 million.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    I think it's hard to do it directly after testing a lot. Maybe there is a workaround. As you said in your post, you track the KPI to date. So you could have spread the 5 million to the whole year by days. Let's start here.

    1. In my sample, I compare the sales quantity of the year 2008 to the year 2007. 

    2. Create a table named "KPItimes" like this.

    Times

    0.6
    0.7
    0.8
    0.9
    1
    1.1
    1.2
    1.3
    1.4

    3. Measure to sum the quantities.

    SalesQuantitySum =
SUM ( 'Sales'[Quantity] )

    4. Measure to sum the quantities of last year. (To you, it's 5 million. This step is the key point of this workaround)

    SalesQuantityLastyear =
IF (
    HASONEVALUE ( 'KPItimes'[Times] ),
    MIN ( KPItimes[Times] )
        * CALCULATE ( SUM ( Sales[Quantity] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
    CALCULATE ( SUM ( Sales[Quantity] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

    5. Create KPI visual and a slicer of 'KPItimes'[Times]. Finally, we can change the value of the slicer to change the goal from 3 million to 7 million.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale