Forum Discussion

EaglesTony's avatar
EaglesTony
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Need help in collapsing table on itself

Hi,

 

  I have the following table:

 

  FeatureKey      AddedOrRemovedTotal       Count

  ABC                  RemovedFromParent          12

  ABC                  AddedToParent                     5

  DEF                  RemovedFromParent             2

  IJK                    AddedToParent                      8

 

I need to collapse these into a single row based on FeatureKey as such:

 

  FeatureKey      TotalRemoved       TotalAdded

  ABC                  12                         5

  DEF                  2                           0

  IJK                    0                           8

 

I'm ok if this is in DAX.

Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    EaglesTony,

     

    Try this calculated table:

     

    NewTable = 
ADDCOLUMNS (
    VALUES ( BaseTable[FeatureKey] ),
    "TotalRemoved",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "RemovedFromParent"
        ),
    "TotalAdded",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "AddedToParent"
        )
)

     

     

    Add "+ 0" if you want 0 instead of blank:

     

    NewTableWithZero = 
ADDCOLUMNS (
    VALUES ( BaseTable[FeatureKey] ),
    "TotalRemoved",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "RemovedFromParent"
        ) + 0,
    "TotalAdded",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "AddedToParent"
        ) + 0
)

5 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    1 year ago

    EaglesTony,

     

    Try this calculated table:

     

    NewTable = 
ADDCOLUMNS (
    VALUES ( BaseTable[FeatureKey] ),
    "TotalRemoved",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "RemovedFromParent"
        ),
    "TotalAdded",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "AddedToParent"
        )
)

     

     

    Add "+ 0" if you want 0 instead of blank:

     

    NewTableWithZero = 
ADDCOLUMNS (
    VALUES ( BaseTable[FeatureKey] ),
    "TotalRemoved",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "RemovedFromParent"
        ) + 0,
    "TotalAdded",
        CALCULATE (
            SUM ( BaseTable[Count] ),
            BaseTable[AddedOrRemovedTotal] = "AddedToParent"
        ) + 0
)
  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    1 year ago

    Hi EaglesTony 

     

    You can also reach the same results via Power Query

     

    • Open Power Query Editor: Go to "Transform data" in Power BI Desktop.
    • Select your table (FeatureTable).
    • Pivot the table:
      • Select the AddedOrRemovedTotal column.
      • Go to the "Transform" tab in the Power Query ribbon.
      • Click on "Pivot Column".
      • In the "Pivot Column" dialog:
        • For "Values column", select Count.
        • Click "OK".
    • Rename the resulting columns: Power Query will likely create columns named "RemovedFromParent" and "AddedToParent". You can rename these to "TotalRemoved" and "TotalAdded" respectively.

    If required, you can use replace value to replace null by 0

     

    • EaglesTony's avatar
      EaglesTony
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

      The only issue I can see with this is speed, as the table as close to 1MB of data and already has a group by in it.

      • Cookistador's avatar
        Cookistador
        Icon for Super User rankSuper User
        1 year ago

        You can also add two calculated columns

         

        TotalAdded =
        CALCULATE(
            SUM('Table'[Count])+0,
            FILTER(
                'Table',
                'Table'[AddedOrRemovedTotal] = "AddedToParent"
            )
        )
         
         
        TotalRemoved =
        CALCULATE(
            SUM('Table'[Count])+0,
            FILTER(
                'Table',
                'Table'[AddedOrRemovedTotal] = "RemovedFromParent"
            )
        )
         
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi EaglesTony 

     

    You can use Pivot in the query editor

    Here's a sample code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRCkrNzS9LTXErys8NSCxKzSsBihkaKcXqwBQ4pqSkpoTkwyVNwXIurm44NEP0enp5Y9FroRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FeatureKey = _t, AddedOrRemovedTotal = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FeatureKey", type text}, {"AddedOrRemovedTotal", type text}, {"Count", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[AddedOrRemovedTotal]), "AddedOrRemovedTotal", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"RemovedFromParent", "AddedToParent"})
in
    #"Replaced Value"