Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create measure to obtain fillrate in days

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.

 

TicketsDays to resolve
A0
B2
C3
D0
G2
F0
H0
I4
J6
K9
L2
M1

 

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 TicketsAvg**bleep** Avg90% Fill Rate
0433%33%0
118%42%0
2325%67%0
318%75%0
418%83%0
500%83%5.8
618%92%0
700%92%0
800%92%0
918%100%0

 

Thanks,

CS

 

1 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.

View solution in original post

14 REPLIES 14
v-easonf-msft
Community Support
Community Support

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
Not applicable

@v-easonf-msft 

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

 

Thanks,

CS

 

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
Not applicable

@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
Not applicable

@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

 

 

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
Not applicable

@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

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
Not applicable

@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

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
Community Support
Community Support

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
Super User
Super User

@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
Not applicable

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
Super User
Super User

Can you explain these calculations?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.