cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Need Dax formula to get 90 Fill Rate

Hello Team,

I need DAX formula to get as below excel calculation

On D2 cell given as =C2

On D3 cell given as =D2+C3 and it should continue till last row

On E2 cell given as =IF(AND(D2<0.9,D3>=0.9),A2+(A3-A2)*(0.9-D2)/(D3-D2),0) and it should continue till last row.

 Row Labels Count of Sotat Count of Sotat2 Fill Rate 90 0 8965 73.96% 73.96% 0 1 1035 8.54% 82.49% 0 2 860 7.09% 89.59% 2.076497696 3 651 5.37% 94.96% 0 4 129 1.06% 96.02% 0 5 77 0.64% 96.66% 0 6 62 0.51% 97.17% 0 7 115 0.95% 98.12% 0 8 40 0.33% 98.45% 0 9 34 0.28% 98.73% 0 10 46 0.38% 99.11% 0 11 11 0.09% 99.20% 0 12 14 0.12% 99.32% 0 13 22 0.18% 99.50% 0 14 23 0.19% 99.69% 0 15 11 0.09% 99.78% 0 20 3 0.02% 99.80% 0 21 13 0.11% 99.91% 0 29 4 0.03% 99.94% 0 30 1 0.01% 99.95% 0 33 1 0.01% 99.96% 0 41 3 0.02% 99.98% 0 52 2 0.02% 100.00% 0

Regards,

AK

1 ACCEPTED SOLUTION
Resolver II

Create a calculated column for column 😧

ColumnD = VAR CurrentRow = YourTable[Index] // Replace 'YourTable' with the actual name of your table and 'Index' with your table's index column

RETURN

IF( CurrentRow = 2, // For D2 (second row), take the value from column C YourTable[C], YourTable[ColumnD] + YourTable[C] // For other rows, add the value in column C to the previous row's value in column D

)

Create a calculated column for column E:

ColumnE = VAR CurrentRow = YourTable[Index] // Replace 'YourTable' with the actual name of your table and 'Index' with your table's index column

VAR PreviousRowValueD = CALCULATE(MAX(YourTable[ColumnD]), FILTER(YourTable, YourTable[Index] = CurrentRow - 1))

RETURN

IF( CurrentRow > 2 && YourTable[ColumnD] < 0.9 && PreviousRowValueD >= 0.9, YourTable[A] + (YourTable[A] - EARLIER(YourTable[A])) * (0.9 - EARLIER(YourTable[ColumnD])) / (YourTable[ColumnD] - EARLIER(YourTable[ColumnD])), 0

)

Replace 'YourTable' with the name of your table, and 'Index' with your table's index column if you have one.

5 REPLIES 5
Helper I

Hello Nirali,

Sorry for coming back.

As the table which I created it's not showing edit query, I have managed to apply first formula in Measure.

Create a calculated column for column E:

ColumnE = VAR CurrentRow = YourTable[Index] // Replace 'YourTable' with the actual name of your table and 'Index' with your table's index column

VAR PreviousRowValueD = CALCULATE(MAX(YourTable[ColumnD]), FILTER(YourTable, YourTable[Index] = CurrentRow - 1))

RETURN

IF( CurrentRow > 2 && YourTable[ColumnD] < 0.9 && PreviousRowValueD >= 0.9, YourTable[A] + (YourTable[A] - EARLIER(YourTable[A])) * (0.9 - EARLIER(YourTable[ColumnD])) / (YourTable[ColumnD] - EARLIER(YourTable[ColumnD])), 0

)

Resolver II

Hey @krishak77

Want to inform you that you have marked your message as a solution.

No appreciation for my solution 🙄

Helper I

Heay Nirali,

Really Appreciate your support and its woking as expected.

Thanks a lot

Resolver II

Create a calculated column for column 😧

ColumnD = VAR CurrentRow = YourTable[Index] // Replace 'YourTable' with the actual name of your table and 'Index' with your table's index column

RETURN

IF( CurrentRow = 2, // For D2 (second row), take the value from column C YourTable[C], YourTable[ColumnD] + YourTable[C] // For other rows, add the value in column C to the previous row's value in column D

)

Create a calculated column for column E:

ColumnE = VAR CurrentRow = YourTable[Index] // Replace 'YourTable' with the actual name of your table and 'Index' with your table's index column

VAR PreviousRowValueD = CALCULATE(MAX(YourTable[ColumnD]), FILTER(YourTable, YourTable[Index] = CurrentRow - 1))

RETURN

IF( CurrentRow > 2 && YourTable[ColumnD] < 0.9 && PreviousRowValueD >= 0.9, YourTable[A] + (YourTable[A] - EARLIER(YourTable[A])) * (0.9 - EARLIER(YourTable[ColumnD])) / (YourTable[ColumnD] - EARLIER(YourTable[ColumnD])), 0

)

Replace 'YourTable' with the name of your table, and 'Index' with your table's index column if you have one.

Helper I

Hello Nirali,

Thanks a lot for the solution

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.