Get DAX formula in Measure

[krishak77]

Hello All,

Can anybody please provide me DAX formula to apply only in Measure and not in Add Column for the below requirement?

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	FR_Per	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

[lbendlin]

measures are working with aggregated data. Your calculations clearly work on row level so they need calculated columns or (more likely) Power Query code to work.

[krishak77]

Hi @lbendlin ,

Thanks for your response.

The about table is pivot table from large data of Excel, and I need this to calculate in Power Bi only for showing above table by using Matrix chart in Power Bi.

So could you please help to provide Dax formula to applied in measure?

Thank you in advance.

[Anonymous · Accepted solution]

Hi @krishak77 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Create a calculated column as below to get it:

Fill Rate 90 = 
VAR _nextlabel =
    CALCULATE (
        MIN ( 'Table'[Row Labels] ),
        FILTER ( 'Table', 'Table'[Row Labels] > EARLIER ( 'Table'[Row Labels] ) )
    )
VAR _nextfrper =
    CALCULATE (
        MAX ( 'Table'[FR_Per] ),
        FILTER ( 'Table', 'Table'[Row Labels] = _nextlabel )
    )
RETURN
    IF (
        [FR_Per] < 0.9
            && _nextfrper >= 0.9,
        'Table'[Row Labels] + ( _nextlabel - 'Table'[Row Labels] ) * ( 0.9 - 'Table'[FR_Per] ) / ( _nextfrper - 'Table'[FR_Per] ),
        0
    )

Best Regards