topic How to substract a row to all the row in the filtered table ? in DAX Commands and Tips

How to substract a row to all the row in the filtered table ?

Anonymous — Thu, 22 Jun 2023 17:35:47 GMT

Hello the community,

I have filtered my table using slicers, now I need to substract a specific row to all the rows in the filtered table. In fact, in the new table I have a unique row with 1 as a type (type column) and I need to substract it to all the others rows.

Nonetheless, it seems tricky in Power BI to do that.

The most closely i've is to use SUMX to substract, but instead of substract it to all the rows its only substract it to the same row.

The idea that I got is to duplicate that row in a new table but as the filtered table is dynamic due to the slicers it seems impossible for my level.

Whould you please give me some tips to do that please ?

Thanks all

Re: How to substract a row to all the row in the filtered table ?

Sahir_Maharaj — Fri, 23 Jun 2023 20:44:40 GMT

Hello Anonymous,

1. Create a measure that calculates the value you want to subtract from the other rows.

SubtractionValue = CALCULATE(SUMX(MyTable, MyTable[Value]), MyTable[Type] = 1)

2. Create another measure that subtracts the SubtractionValue from the Value column for each row.

Result = MyTable[Value] - [SubtractionValue]

Do not hesitate to let me know if you might need further assistance.

Re: How to substract a row to all the row in the filtered table ?

Anonymous — Mon, 26 Jun 2023 14:39:54 GMT

Thank you, but I'm still facing to the same problem when I use SUMX. As you can see in the screenshot, It's it doesn't compute the soustraction only for the first line it's like the value was related to the value it points to.