Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
8 years ago
Solved

Combining 2 tables and Summarize

Goodday,

 

Im hoping someone can help me out on this one.

I have a table where ALL the incidents are registered and categorized based on 'Soort Schade' and a few extra colums.

 

What i want to do is make a new table and a colum that lists all the unique values of the column 'Infrastructuur' and then counts all the times that value is in that column

 

So basically i now have:

 

Damage                 Infrastructuur

1.                             Electric

2.                             Gas

3.                             Water

4.                             Water

 

and i want a new table that combines that data into:

 

Infrastructuur              Number of tickets

Electric                            1

Gas                                  1

Water                              2

 

then i want to calculate other stuff but that is for later :) 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi RvdHeijden,

     

    In this scenario, you can adjust the formula like below. :smileyhappy:

    New Table = 
SUMMARIZE (
    Data,
    Data[Infrastructuur],
    "Civiel (BIS)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (BIS)" ) ),
    "Civiel (Tuin)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (Tuin)" ) )
)

     

    Regards

4 Replies

  • Datatouille's avatar
    Datatouille
    Solution Sage
    8 years ago

    So basically you want to summarize this table :

    Damage                 Infrastructuur

    1.                             Electric

    2.                             Gas

    3.                             Water

    4.                             Water

     

    Let's assume the table above is called 'Data'.

     

    If the 'Data' table comes from a DAX query, then it won't be available in Power Query and you need to use a DAX method:

     

    Use this DAX query to create your desired table ('Modeling' tab and then 'New Table' in Power BI Desktop):

    New Table = ADDCOLUMNS( SUMMARIZE(Data;Data[Infrastructuur]);"Number Of Tickets"; CALCULATE(COUNTROWS(Data) ))

    if your 'Data' table is available in Power Query, you can use a the 'Group by' feature and chose count rows in the 'operation' section.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi RvdHeijden,

     

    The formula(DAX) below should also work. :smileyhappy:

    New Table =
SUMMARIZE (
    Data,
    Data[Infrastructuur],
    "Number Of Tickets", COUNTROWS ( Data )
)

     

    Regards

    • RvdHeijden's avatar
      RvdHeijden
      Post Prodigy
      8 years ago

      v-ljerr-msft

      it looks like i need to change my question a bit because i wasn't clear.

      in both you formulas you added the colum 'Number of Tickets' but that is not a column.

       

      my table where the 'Infrastructuur' column is in just lists the types, the new table should 'calculate' the number of times a type of Infrastructuur is in the table

      Besides the column 'infrastructure' i would like to add more colums such as 'teamtype' or whatever how do i add this to the formula ?

       

      so basically

       

      infrastructuur        TeamType

      gas                           Civiel (BIS)

      electric                     Civiel (BIS)

      water                        Civiel (Tuin)

      water                        Civiel (BIS)

      water                        Civiel (Tuin)

      electric                     Civiel (Tuin)

       

      the new table should calculate the times 'water' is in the other table to return the result

       

                     Civiel (BIS)          Civiel (Tuin)

      gas                 1

      electric           1                             1

      water              1                             2 

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi RvdHeijden,

         

        In this scenario, you can adjust the formula like below. :smileyhappy:

        New Table = 
SUMMARIZE (
    Data,
    Data[Infrastructuur],
    "Civiel (BIS)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (BIS)" ) ),
    "Civiel (Tuin)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (Tuin)" ) )
)

         

        Regards