Change KPI target in report view

[Anonymous]

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?

[v-jiascu-msft · Accepted solution]

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