Forum Discussion
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 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.
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=_p9E8fiDRDwIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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
7 Replies
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/1447523Ritaf1983 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.
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=_p9E8fiDRDwIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
