topic Re: Subtract every row by specific row value in App Development

Subtract every row by specific row value

jaimeoconor — Sat, 16 Nov 2024 05:09:31 GMT

Hi,

I have, for example, the following data:

Asset Performance

Asset 1 5%

Asset 2 3%

Asset 3 8%

Asset 4 10%

I would like to create a table showing every Asset and the result of Asset x Performance - Asset 1 Performance. The result should be the following:

Asset 1 0%

Asset 2 -2%

Asset 3 3%

Asset 4 5%

I wrote Sum([Performance])-sum({<Asset={'Asset 1'}>}[Performance]) and, as a result, only Asset 1 is being subtracted.

Any ideas?

Many thanks in advance!

Re: Subtract every row by specific row value

sunny_talwar — Fri, 26 Jul 2019 18:00:58 GMT

You are missing the TOTAL qualifier from the second expression

Sum([Performance]) - Sum(TOTAL {<Asset = {'Asset 1'}>}[Performance])

Re: Subtract every row by specific row value

jaimeoconor — Fri, 26 Jul 2019 18:48:42 GMT

Thanks Suuny, it worked. Now I have an extra problem. There are Assets that have missing values, no performance instead of 0%. So, following the example, if Asset 5 has no performance, I need to see -5% as a result, but now I see nothing.

Any idea?

Thanks!

Re: Subtract every row by specific row value

sunny_talwar — Fri, 26 Jul 2019 19:52:23 GMT

Try this

RangeSum( Sum([Performance]), -Sum(TOTAL {<Asset = {'Asset 1'}>}[Performance]) )

Re: Subtract every row by specific row value

jaimeoconor — Fri, 26 Jul 2019 20:44:21 GMT

Hi Sunny,

It didn't work, but we can keep on this in other question.

Many thanks

Re: Subtract every row by specific row value

sunny_talwar — Mon, 29 Jul 2019 11:19:33 GMT

Would you be able to share a sample to show what exactly you are seeing?

Re: Subtract every row by specific row value

jaimeoconor — Mon, 29 Jul 2019 14:43:21 GMT

Sorry that I'm not able to show the data. Using the formula above, I see this result:

The first row is OK, since it subtracted the Asset 1 - Asset 1=0. But in the rest of the rows, I would need to do the same in all the rows, not only the values that the row has.

Re: Subtract every row by specific row value

sunny_talwar — Mon, 29 Jul 2019 15:24:45 GMT

Will be difficult to comment without seeing a sample and being able to play around with it.