Forum Discussion

hackfifi's avatar
hackfifi
Helper V
4 years ago
Solved

Comparing Values from One Column in Same Table

Good Day! I have the below Sample Data Table/Query "Detail". I would like to acheive the a table visual shown below ("Expected Result") to compare/reconcile values between two Projects. I can easily...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    hackfifi Ah, details are important. You will want two disconnected Project tables. You can create these using this code:

    ProjectSlicerTable1 = DISTINCT('Table'[Project])

ProjectSlicerTable2 = DISTINCT('Table'[Project])

    Use these for your slicers. The the code becomes:

    Delta measure =
  VAR __Type = MAX('Table'[Type])
  VAR __Compare1 = MAX('ProjectSlicerTable1'[Project])
  VAR __Compare2 = MAX('ProjectSlicerTable2'[Project])
  VAR __A = SUMX(FILTER('Table',[Type]=__Type && [Project] = __Compare1),[Value])
  VAR __B = SUMX(FILTER('Table',[Type]=__Type && [Project] = __Compare2),[Value])
RETURN
  __B - __A
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

hackfifi Seems like something along the lines of:

Delta (A vs B) measure =
  VAR __Type = MAX('Table'[Type])
  VAR __A = SUMX(FILTER('Table',[Type]=__Type && [Project] = "A"),[Value])
  VAR __B = SUMX(FILTER('Table',[Type]=__Type && [Project] = "B"),[Value])
RETURN
  __B - __A