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
  • hackfifi's avatar
    hackfifi
    Helper V
    4 years ago

    Greg_Deckler Thanks for taking time to respond. But my "Project" dropdown (list of 100+ projects) would be dynamic i.e. the user can select ANY two out of 100+ projects. I just provided sample dataset...the current data model has over 70+ columns. 
    I am not sure if i need to have two project tables...so user can single select one project from each "Project" filter? i.e. Select Project "A" & Select Project "B"?
     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      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
      • hackfifi's avatar
        hackfifi
        Helper V
        4 years ago

        Thanks Mate - That worked. 

        Cheers for your help