Need Help! KPI Visual (Indicator / Trend / Target) issue

[AFinster]

Greetings all,

(Sorry if this is a bit long winded ...)

I am attempting to build a (hopefully!) basic KPI Visual. It is based on a snapshot of a number of companies in our group, and shows Week Number, Company Code, Staff Type (Staff / Contractor), Department, and then a series of totals (e.g. Total Staff, On-site, Off-Site, Off-Duty)

The week number does not correlate with the week number of a calendar, it represents the number of weeks since the records were started (see sample data below). 

There is a 1:M relationship between the Week table and the Workforce table.

Examples of what I want to do:

(Note: Dashboard has a slicer to select the Week Number for eaxample "Week 8" which correlates to the value of 😎

1. a KPI card that shows Total Workforce (for the selected week number 😎 => SUM(Total Staff) where Week Number = 8, with the "Goal" being the SUM(Total Staff) for the previous week (selected week number - 1 = 7).

2. a KPI card that shows Total Contractor (for the selected week number = 😎 that are of category "Contractor" => SUM(Total Staff) WHERE Week NUmber = 8 and Staff Type = "Contactor", with the "Goal" being the SUM(Total Staff) WHERE Staff Type = "Contactor" for the previous week (selected week number - 1 = 7).

- In #1, I can get the Total Workforce for the selected week by using the following Measure:

Total Workforce = CALCULATE( sum(Workforce[Total Staff]), FILTER(ALLSELECTED('Workforce'),'Workforce'[Week No] = MAX(Workforce'[Week No])) )

But when I query the Goal, I get "(Blank)"

Previous Week Total Workforce = CALCULATE(SUM(Workforce[Total Staff]), FILTER( ALLSELECTED('Workforce'), 'Workforce'[Week No] = MAX(Workforce'[Week No]) - 1 ) )

If Week 8 is selected, I would expect to see:

205 This week

222 Last week

- For #2 (Contractors only), I'm stumped on the contruction of the Measure, but the results should be:

75 This week

92 Last week

Problem #3

Once each of the above measures are constructed, the slot in to the "Indicator" and "Target Goals" fields for the KPI Visual. My question is what should be added in to the "Trend Axis" field???

Any ideas welcome and appreciated!

Weeks table

Workforce table

[AFinster]

Hi all,

Firstly, thanks for your quick replies. I have been on a pretty tight delivery deadline to deliver my dashboard (COVID related) so I have had to create some summary tables in my data source in the format that I wanted them. It's not ideal, but it got me out of the immediate situation.

Ideally, I want to use a Power BI / DAX solution to the problem, so when I have some spare cycles, I'll review your answers in detail and let you know my findings...

[v-kelly-msft]

Hi  @AFinster ,

Pls keep me informed if my solution doesnt work for you.

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @AFinster ,

Is below what you need?

If so,you only need a measure as below:

Previous Week Total Workforce = 
IF(ISFILTERED('Table'[Staff type]),CALCULATE(SUM('Table'[Total Staff]),FILTER(ALL('Table'),'Table'[Week No.]=MAX('Table'[Week No.])-1&&'Table'[Staff type]=MAX('Table'[Staff type]))),CALCULATE(SUM('Table'[Total Staff]),FILTER(ALL('Table'),'Table'[Week No.]=MAX('Table'[Week No.])-1)))

#3 In the fields ,you should set as below:

Here is a reference about KPI which may help.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AllisonKennedy]

You'll want to put Week No in the trend axis. You may also want to start this as a matrix visual and make sure it is sorted correctly before turning into KPI visual: 

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi

The reason you're getting blank for the Goal is because your current measure has too many filters: 

Previous Week Total Workforce = CALCULATE(SUM(Workforce[Total Staff]), FILTER( ALLSELECTED('Workforce'), 'Workforce'[Week No] = MAX(Workforce'[Week No]) - 1 ) )

The ALLSELECTED('Workforce') is filtering the workforce table to only show the currently selected Week No.

Then 'Workforce'[Week No] = MAX(Workforce'[Week No]) - 1 is filtering to show the previous Week No. 

There are no records that exist in both the selected Week No AND the previous Week No, so your goal shows blank. 

You need to either use a filter that SHIFTS the current filter context (like DATEADD or Datesbetween) or try adding an ALL(Workforce[Week No]) into the Goal somewhere.