Need Dax formula to get 90 Fill Rate

[krishak77]

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

Thank you in advance

Regards,

AK

[krishak77]

Hello Nirali,

Sorry for coming back.

could you please help to apply below formula in Measure?

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

)

[nirali_arora]

Hey @krishak77 

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

No appreciation for my solution 🙄

[krishak77]

Heay Nirali,

Really Appreciate your support and its woking as expected.

Thanks a lot

[nirali_arora · Accepted solution]

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.

[krishak77]

Hello Nirali,

Thanks a lot for the solution