Utilization rate

[PeterGarant]

Hi all

I'm still pretty new to Power BI, so there might be a very simple solution for my problem.

I'm trying to calculate the utilization rate of some of our items. I've made it so that it takes into account how old the item is (Shouldn't count all days in a year, if it was created later in that year).

Right now I'm just looking at one item group from 2016, but one of those items is created in 2017 and is giving me some problems. I could just take it out of the dataset, but I would love to be able to get all the data out so that I can make the report more dynamic at a later date.

Here is what I have at the moment:

 The top table shows it for the item group and the bottom for each item in the group. If i remove the item with negative age, then it all seems fine. Here is the measure that I'm using to calculate the utilization rate:

Utilization rate = Sum (Turnover[Rent days])/(DISTINCTCOUNT(Items[Itemnumber])* if (DISTINCTCOUNT('Date'[Date])>Items[Age];Items[Age];DISTINCTCOUNT('Date'[Date])))

The problem is that I would like to filter out the one with the negative age, but without removing it from the data. If I just filter it out in the item table, then it's fine, but it is still calculated in the item group table and I can't filter it out in the same way without it just going blank.

If I filter out the item that is making the problems then I get the following:

The problem I have in my current measure is that in the total it will always use 366 days (2016 was a leap year), but 3 of the items should actually be calculated from their actual age wich is 337 days.

So what I am asking is

1. What is the best way to filter out the unwanted data without removing the data from the dataset 
2. What is the best way to calculate the totals, as right now thei are actually done wrong, but I couldn't find a better way

I don't know if this is enough info, so if you need anything else from me just ask. And thank you all for any info you are able to provide.

[PeterGarant]

Bump, anybody had a chance to look at this? I still havn't found a good way to solve my problem 😞

[v-huizhn-msft]

Hi @PeterGarant,

After research, what part of your report will be dynamic according to your requirement?

Best Regards,
Angelia

[PeterGarant]

Hi @v-huizhn-msft

Well, right now I'm only selecting for 2016, but in the end I would like to have all years or at least from 2004 in the data. So figureing out how to make it dynamic, and then for each year would be the goal in the end. But right now it's more just to figure out how to make the totalts correct.

[v-huizhn-msft · Accepted solution]

Hi @PeterGarant,

If you select 2016, you need to add a [year] as slicer. Then create a measure using the similar formula.

result=CALCULATE([total],Filter(Table, Table[year]<=ALLSELECTED(Date[year])))

Don't hesitate to ask if you have any questions.

Best Regards,
Angelia

[PeterGarant]

Hi @v-huizhn-msft

I will try it out and see if it helps. Thank you for your help.

[v-huizhn-msft]

Hi @PeterGarant,

Do you mind share your resource table or .pbix file for further analysis? It's hard to reproduce your scenario wittout sample table.

Best Regards,
Angelia

[PeterGarant]

Hi @v-huizhn-msft

Here is a link to the file:

https://www.dropbox.com/s/radpououmbyz1oh/Bel%C3%A6gningsgraderMedDage.pbix?dl=0

Thank you for any insight you might be able to provide. And sorry for the danish words here and there 🙂

I can easily take out the sinner from the data, but as I wrote earlier, I would like to be able to filter it out in the report instead of the in the data, as I would like to make it more flexible in the future.

[CahabaData]

I think the approach to use involves how you define/determine age.  It's odd to see a negative number - and that same thing is what is causing you problems.  So I would change that approach rather than attempt to resolve it via filters or measures up in the visual level.

Typically for machinery aging there is a Start or In Service date - - and then its age is calculated dynamically off of that.  The calendar function will know when it is a leap year so there's no problem there; and if you want to carve out weekends to get a more true in-service age rather than calendar age - - that is possible too.

You'll never have a negative number and this should scale to all of your visuals without further problems.

[PeterGarant]

Hi @CahabaData

The problem is that in this example I am looking at sales from 2016, but i have one item that wasn't created until 2017, so I want it to be in my data, but just not be calculated in the 2016 report. But in the future I would like to make the report dynamic, so that is why i don't want to filter it out in the dataset.