Forum Discussion
difference of rows from same table
Hi,
I have a single table as shown below. the user can select upto two run ids from a slicer. so if the user selected 2,3 i would like to show a table which does Sum(amount; run id = 2) - Sum(amount; run id = 3) grouped by treaty and cal_year. i know it can be done from two different table but is there a way to do this within the same table.
Input data:
| run id | treaty | cal year | amount |
| 1 | A | 2022 | 100 |
| 1 | A | 2023 | 110 |
| 1 | B | 2022 | 120 |
| 1 | B | 2023 | 130 |
| 1 | C | 2022 | 140 |
| 1 | C | 2023 | 150 |
| 2 | A | 2022 | 160 |
| 2 | A | 2023 | 170 |
| 2 | B | 2022 | 180 |
| 2 | B | 2023 | 190 |
| 2 | C | 2022 | 200 |
| 2 | C | 2023 | 210 |
| 3 | A | 2022 | 220 |
| 3 | A | 2023 | 230 |
| 3 | B | 2022 | 240 |
| 3 | B | 2023 | 250 |
| 3 | C | 2022 | 260 |
| 3 | C | 2023 | 270 |
Anonymous
you can try this
Measure =var _id=sumx(FILTER(all('Table'),'Table'[treaty]=max('Table'[treaty])&&'Table'[cal year]=max('Table'[cal year])&&'Table'[run id]=min('Table'[run id])),'Table'[amount])var _id2=sumx(FILTER(all('Table'),'Table'[treaty]=max('Table'[treaty])&&'Table'[cal year]=max('Table'[cal year])&&'Table'[run id]=max('Table'[run id])),'Table'[amount])return _id-_id2pls see the attachment below
3 Replies
Anonymous
you can try this
Measure =var _id=sumx(FILTER(all('Table'),'Table'[treaty]=max('Table'[treaty])&&'Table'[cal year]=max('Table'[cal year])&&'Table'[run id]=min('Table'[run id])),'Table'[amount])var _id2=sumx(FILTER(all('Table'),'Table'[treaty]=max('Table'[treaty])&&'Table'[cal year]=max('Table'[cal year])&&'Table'[run id]=max('Table'[run id])),'Table'[amount])return _id-_id2pls see the attachment belowAnonymous
Create a Measure
AmountDifference =
VAR SelectedRuns = VALUES('Table'[run id])
VAR Run1 = CALCULATE(SUM('Table'[amount]), 'Table'[run id] = MIN(SelectedRuns))
VAR Run2 = CALCULATE(SUM('Table'[amount]), 'Table'[run id] = MAX(SelectedRuns))
RETURN
IF(COUNTROWS(SelectedRuns) = 2, Run1 - Run2, BLANK())Use Treaty and Cal Year as row fields, and place AmountDifference as the value.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
