Formula to substract one row from antoher

[yakamoz007]

I have a table where I have to exclude the AFA value from the materail costs (green fields) and I also have to change the row AFA into a positive value (blue field).

I tried so much and have no ideas, which formula can help me out. :S Any ideas?

[lbendlin · Accepted solution]

Power BI is a reporting tool, not a business process management tool (yet).

Having said that - what is the relationship between the material cost row and the AFA row?  Will the AFA row always be the "next"  row according to the index? Or is there another identifier that needs to be considered? 

What if the AFA row is missing?

I don't think you want to do this in DAX.  This needs to be done in Power Query, or ideally at the data source.

[v-janeyg-msft · Accepted solution]

Hi, @yakamoz007 

I agree with @lbendlin. If you have to do with dax, you can create a calculated column.

Like this:

Column = 
VAR a =
    CALCULATE ( MAX ( 'Table'[Values] ), FILTER ( 'Table', [KNZ NR] = 444 ) )
RETURN
    IF (
        [KNZ NR] = 444
            && [Values] < 0,
        [Values] * ( -1 ),
        IF ( [KNZ NR] = 333, [Values] - a, [Values] )
    )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[yakamoz007]

Thanks a lot!

[lbendlin · Accepted solution]

Power BI is a reporting tool, not a business process management tool (yet).

Having said that - what is the relationship between the material cost row and the AFA row?  Will the AFA row always be the "next"  row according to the index? Or is there another identifier that needs to be considered? 

What if the AFA row is missing?

I don't think you want to do this in DAX.  This needs to be done in Power Query, or ideally at the data source.