Create measure to obtain fillrate in days

[Anonymous]

Hello,

Could somebody help me in getting the performance fillrate in days at 90%. I have the input data as shown below, and would like to know what is the performance in days to resolve the 90% of tickets.

Tickets	Days to resolve
A	0
B	2
C	3
D	0
G	2
F	0
H	0
I	4
J	6
K	9
L	2
M	1

We have excel calculation which results out the performance in days as shown below. 5.8 Days is the output in the given sample.

Days	# of Tickets	Avg	**bleep** Avg	90% Fill Rate
0	4	33%	33%	0
1	1	8%	42%	0
2	3	25%	67%	0
3	1	8%	75%	0
4	1	8%	83%	0
5	0	0%	83%	5.8
6	1	8%	92%	0
7	0	0%	92%	0
8	0	0%	92%	0
9	1	8%	100%	0

Thanks,

CS

[v-easonf-msft]

Hi , @Anonymous 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread?

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft 

Could you kindly provide the same DAX in new measures but not as new column.

Thanks,

CS

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Create mesure as below:

Test = 
var currentOrder = MAX('Sheet1'[order_to_delv])
var nextOrder = CALCULATE(MIN('Sheet1'[order_to_delv]),FILTER(ALLSELECTED(Sheet1),'Sheet1'[order_to_delv]>currentOrder))
var NextRunningTotal =
    SUMX (
        CALCULATETABLE(DISTINCT('Sheet1'[order_to_delv]),
        FILTER (
            ALLSELECTED ( Sheet1),
            Sheet1[order_to_delv] <=nextOrder
        )),
        CALCULATE(
        DIVIDE (
            CALCULATE(COUNT ( Sheet1[order_no] )),
            CALCULATE (
                COUNT ( Sheet1[order_no] ),
                ALLEXCEPT ( Sheet1, Sheet1[hp_receive_date], Sheet1[order_no] )
            )
        )*100)
    )

var CurrentRunningTotal = [RunningTotal]
return IF(CurrentRunningTotal<90 && NextRunningTotal>=90,(currentOrder + (90-CurrentRunningTotal)/(NextRunningTotal-CurrentRunningTotal)),0)

Check below  pbxi file.

pbix attached

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

[Anonymous]

@v-easonf-msft 

Thanks, would you help to get total value of "test" measure when i put on visualization. As now, it returns 0.

Regards,CS

[Anonymous]

@v-easonf-msft 

Thank you very much!

The variable for "NextRunningTotal" is actually doing what it meant to be, but even on the first row. The first value should remain same, and for the next ones it should be as nextrunningtotal.

Could you please help with this.

https://drive.google.com/open?id=1GiqLrk9mCVh8kQ6Tg-XpDceHne1OT61W

Best Regards,

CS

[v-easonf-msft]

Hi , @Anonymous 

Not very clear   the variable for "NextRunningTotal" . Is  "NextRunningTotal"  another measure?

Please explain more  about it . 

It will be better if you can share my the expected result in excel.

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft 

Thanks, please find the attached excel.

https://drive.google.com/open?id=1_9wCC1qBk1P8UKeqt57OhGJ4I9rcXYLQ

The NextRunningTotal is not a new measure, am sorry if have confused you. May be the excel attached might give you clear understanding.

Regards,

CS

[v-easonf-msft]

Hi, @Anonymous 

Could you please tell me whether your problem has been solved?

If not , please tell me more details.

If it is, you can add your reply as Answered to close this thread.

Best Regards,
Community Support Team _ Eason

[Anonymous]

@v-easonf-msft ,

Hello,

Please find the attached excel with data set and required output.

My input will be column A,B,C from input tab.

And output measure should be Fillrate value.

https://drive.google.com/open?id=1k1wAKwGBZI-JSHr6jGbR7LfCv4n77svl

Thanks,

CS

[v-easonf-msft]

Hi,  @Anonymous 

Your table makes me even more confused.😔 

Can you show me the result of the ”Next running total "you want, I can't get anything new from your excel.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi , @Anonymous 

Try measures as below :

Column =
IF (
    'Table 2'[**bleep** Avg] < 0.9
        && CALCULATE (
            SUM ( 'Table 2'[**bleep** Avg] ),
            FILTER ( 'Table 2', 'Table 2'[Days] = EARLIER ( 'Table 2'[Days] ) + 1 )
        ) >= 0.9,
    [Days]
        + ( 0.9 - [**bleep** Avg] )
            / (
                CALCULATE (
                    SUM ( 'Table 2'[**bleep** Avg] ),
                    FILTER ( 'Table 2', 'Table 2'[Days] = EARLIER ( 'Table 2'[Days] ) + 1 )
                ) - [**bleep** Avg]
            ),
    0
)

Here is a demo.

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

[amitchandak]

@Anonymous ,

Few which I can get looking at data. please provide calc logic

Ticket = sum(table[# of Tickets])
Total Ticket = calculate(sum(table[# of Tickets]),all(Table))
Running ticket = calculate(sum(table[# of Tickets]),filter(all(Table),table[Days]<=max(Table[Days])))

Avg =divide([Ticket],[Total Ticket])
**bleep** Avg =divide([Running ticket],[Total Ticket])

Appreciate your Kudos.

[Anonymous]

Hi,

Formula used for fillrate

=IF(AND(I2<0.9,I3>=0.9),($F2+(0.9-I2)/(I3-I2)),0)

Where Column I is **bleep** AVG and F is Days.

Should i create a new table and bring in the # of tickets according to day and then proceed with the calculation measure?

Regards,

CS

[amitchandak]

Can you explain these calculations?