topic Re: Stretch targets that total to KPI in DAX Commands and Tips

Stretch targets that total to KPI

EWBWEBB — Fri, 26 Jan 2024 11:51:21 GMT

I'm trying to share KPI figures with my business around workforce %. So what % of the business is considered talent or has had a promotion etc.

We have an overall business target of, lets say, 25% of the workforce is ....

In simple terms

The business has 1000 people

There are 10 departments with 100 people in each

Each department has 10 teams with 10 people in each

So 2.5 in each team should be...

Rolled up in a visual this means 250 people to achieve target.

What I would like to do is stretch this so each department has a target of 30% but when displayed in a visual the overall target shows as 25%. So should a team miss the KPI they are looking at by a small margin, business still achieves target.

However, I always want the business target to remain at 25% and report performance against that.

Link below to example file

https://we.tl/t-zFnnbbfFm5

Would hope to achieve this with DAX and would want to apply to range of visuals (most likely bar/line/matrix/card)

I will have all the data in a table with raw values for total heads and count to calculate the perctages, 1 row per team.

Any ideas on how to achieve this?

Re: Stretch targets that total to KPI

speedramps — Sat, 27 Jan 2024 15:12:57 GMT

We want to help you but your description is too vaugue. Please write it again clearly.

Remeber we are Power BI experts and want to help but dont know what you want your reports to do.

Explain clearly which columns are targets and which are actuals and their context level.

Please just give a simple non technical functional decscription of what you want, then let us suggest the solution. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confussion.

Rename columns to user friendly names.
Also provide the example desired output, with a clear description of the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gerrting free expert help, so please put lots of proper effort to asking questions and providing example.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

Re: Stretch targets that total to KPI

EWBWEBB — Sat, 27 Jan 2024 20:51:22 GMT

Thanks speedramps - I think in trying to be clear I ended up not being. So let's try again.

In the file below:

https://we.tl/t-UmX3rPiEcv

We have the 1 Row per team:

With the columns

Team: A teams name

Department: The department the teams sit under

Business: The business the departments sit under

Headcount: How many people in each Team.

CurrentTalent: How many in each team are identified as talent

TargetTalent_For25%: This is the value each team needs to achieve which rounds up to a business target of 25%

TargetTalent_ForSharing: This is the value I would like to use to calculate the % in visuals per team

%Target: this is the % target I would actually see in a visual - I would calculate this with DAX just here for clarity.

What I want to achieve is:

When using the [TargetTalent_ForSharing] data points each team will see the relevant % (form a measure) however using these values the business target would come out at 35%.

I want the business target (or the grand total) to always be 25% regardless of what the values of the team total to, when using [TargetTalent_ForSharing].

But when someone expands a matrix table or drills down in a graph though, I want them to see the % generated by using the [TargetTalent_ForSharing] data.

% calcuated by DIVIDE(SUMX(TargetTalent_ForSharing),SUMX(Headcount),0)

In super simple terms I want to force the total to always = 25% regardless of what the actual team values are.

Re: Stretch targets that total to KPI

speedramps — Sun, 28 Jan 2024 21:17:17 GMT

Please try this solution ...

Click here to download PBIX from Onedrive

It does EXACTLY what you asked for

"I want the business target (or the grand total) to always be 25% ... but when someone expands a matrix table or drills down in a graph though, I want them to see the % generated by using the [TargetTalent_ForSharing] data.

If you want somethings different then please politely quickly accept this solution (because I have done what was asked) and raise a new ticket with clear description and examples of what you wants. If you quote speedramps in your new ticket then I will receive an automated notification and will be happy to help.

How it works ...

use the ISFILTERED command to output 25 on the grand total

Answer = IF(ISFILTERED(yourdata[Business]), SUM(yourdata[TargetTalent_ForSharing]), 25 )

Re: Stretch targets that total to KPI

EWBWEBB — Wed, 31 Jan 2024 11:34:48 GMT

Thanks speedramps this does meet the brief.

Although as with many things, when reviewing realised I hadn't accounted for the behaviours of this in the visual (part of the learning curve).

But your solution did get me to follow through in understanding the question I needed to ask.