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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
krishak77
Helper I
Helper I

Get DAX formula in Measure

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 LabelsCount of SotatCount of Sotat2FR_PerFill Rate 90
0896573.96%73.96%0
110358.54%82.49%0
28607.09%89.59%2.076497696
36515.37%94.96%0
41291.06%96.02%0
5770.64%96.66%0
6620.51%97.17%0
71150.95%98.12%0
8400.33%98.45%0
9340.28%98.73%0
10460.38%99.11%0
11110.09%99.20%0
12140.12%99.32%0
13220.18%99.50%0
14230.19%99.69%0
15110.09%99.78%0
2030.02%99.80%0
21130.11%99.91%0
2940.03%99.94%0
3010.01%99.95%0
3310.01%99.96%0
4130.02%99.98%0
5220.02%100.00%0
     
     
     
     
     
     
     
     
     
     
     

 

Thank you in advance

 

Regards,

AK

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

vyiruanmsft_0-1696930769766.png

Best Regards

Community Support Team _ Rena
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

3 REPLIES 3
lbendlin
Super User
Super User

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.

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.

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
    )

vyiruanmsft_0-1696930769766.png

Best Regards

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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