Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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-_id2
     
     
    pls see the attachment below
     
     
Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

Anonymous 

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

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

thanks for this but i get an erorr saying