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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

I recreated your Data Table and saved it as Book12.xlsx and performed the below transformation in the query editor.

let
Source = Excel.Workbook(File.Contents("C:\Book12.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project", type text}, {"Type", type text}, {"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Project]), "Project", "Value", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A", "B", "C"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Type", "Project"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"C"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Delta (A vs B)", each [B]-[A])
in
#"Added Custom"

 

Below is the resulting table.

 

Hope this helps.

 

  • hackfifi's avatar
    hackfifi
    Helper V
    4 years ago

    Thanks Anonymous  - Unfortunately, i cannot modify the table in power query as i have over 100 projects. I just provided a sample dataset; and there are over 70+ columns already and there are a lot of measure based on the current power query. I need to work out a "measure". Cheers again for your time.