cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

KPI MTM Change Goals

Hi!  I'm hoping for a little bit of help with something that has confounded me...

 

I have a dashboard that presents inventory balances and MTM change due to pricing and volume as well as total MTM change for inventory as a whole.

Capture.PNG

 

I'm presenting using the KPI visualization.  My goal is to present any increase is bad and any decrease is good.  Best to explain this in two parts...

 

With total inventory on order (the left most KPI) it works exactly as expected...indicator is current month balance, target is last month balance.  And I'm using the same current/last month logic with the remaining three but the logic isn't correct.  Look at MTM volume change...it's negative despite declining by $121.4M (because the previous month it declined a whole lot more).

 

I've tried creating a measure where [Change Goal = 0] but that results in this...

Capture.PNG

 

Can anyone suggest how to make the KPI indicator to show "good" if any decline and "bad" if any increase?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: KPI MTM Change Goals

Ok!  So I figured this out.  But I don't completely understand why this solution worked.  If anyone would like to offer an explanation, I'd love to understand why.

 

Solution:

  1. Created a column called Change Goal set to a constant = 0
  2. Created a measure called Inventory Change Goal = SUM([Change Goal])
  3. Used the measure Inventory Change Goal as the Target in the KPI visualization.

And the expected results were achieved...compare this screen snip to the one at the very beginning of the thread.

Annotation 2019-09-08 143642.png

 

Can anyone explain why?  It seems unnecessary to have to add a field to 60M records of data to achieve something so simple (not to mention wasted RAM).  Thank you!

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: KPI MTM Change Goals

hi, @littlemojopuppy 

I'm a little confused by your description. Do you want to show "Good" or "Bad" in the KPI visual?

and what is your expected output.

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Re: KPI MTM Change Goals

Right now, the "good" and "bad" is determined relative to the previous month, which is why MTM volume change is shown as bad (because while it declined, it didn't decline as much as the previous month).

 

Goal is ANY decline is good, and ANY increase is bad.  If anyone could provide an idea how to make this happen I'd appreciate it!

Community Support Team
Community Support Team

Re: KPI MTM Change Goals

hi, @littlemojopuppy 

Could you share some sample data or a simple sample pbix file and your expected output?

That will be a great help.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: KPI MTM Change Goals

Hi!  Sorry for the delay...

 

Here's the visual for Inventory on Order and the data in it.

Annotation 2019-07-23 190403.pngAnnotation 2019-07-23 190446.png

If you look at the data, there is an increase from January to February of $20.0M, which is "bad".  And that is reflected accurately.

 

Here is the KPI visualization and data for the MTM Volume Change for Inventory on Order...

Annotation 2019-07-23 190828.pngAnnotation 2019-07-23 190843.png

Part of the $20.2M increase in inventory was due to holding ($121.4M) less stuff in inventory from January to February, which should be considered a "good" thing.  But it's showing up as "bad" because the change from the previous month (which is used as the target goal) was less than the previous month ($555.6M).  Note that the difference between the overall change of $20.2M and ($121.4M) change due to volume is the value of the inventory actually held at the end of February was worth $141.6M more than at the end of January.

 

I know this is a little confusing because inventory is an actual balance that can be seen, touched and counted.  But I'm trying to measure the change from month to month due to different factors or overall in the other three KPIs, not an actual inventory balance.

 

What I'm trying to achieve is ANY increase from month to month due to volume, pricing or overall is "bad".  And conversely, ANY decrease is "good", regardless of the change measured in the previous month.  I tried creating a measure [Change Goal = 0] to use as the Target Goal, and the result of that was the KPI visualization showing "(Blank)".

 

Tried doing some research but didn't find anything about absolute change being good or bad, just change relative to a previous month.

 

I hope my explanation is making sense!  Thanks for any help!  Smiley Happy

Re: KPI MTM Change Goals

I apologize for editing this a couple of times...trying to explain something that is not very straight-forward as succinctly as possible...

Re: KPI MTM Change Goals

Ok!  So I figured this out.  But I don't completely understand why this solution worked.  If anyone would like to offer an explanation, I'd love to understand why.

 

Solution:

  1. Created a column called Change Goal set to a constant = 0
  2. Created a measure called Inventory Change Goal = SUM([Change Goal])
  3. Used the measure Inventory Change Goal as the Target in the KPI visualization.

And the expected results were achieved...compare this screen snip to the one at the very beginning of the thread.

Annotation 2019-09-08 143642.png

 

Can anyone explain why?  It seems unnecessary to have to add a field to 60M records of data to achieve something so simple (not to mention wasted RAM).  Thank you!

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 198 members 2,331 guests
Please welcome our newest community members: