Forum Discussion

iddy's avatar
iddy
Frequent Visitor
3 years ago
Solved

Creating summary Table with different filters

Hi All,

 

I need help in creating some sort of a summary table based on the main table.

 

I have a data table where it has a list of tickets (incidents) with dates of when it is opened and resolved.

I then used a simple formula to calculate the week number of each of the date columns.

 

I would like to create a separate summary table the counts how many tickets are opened and closed in each week.

The problem that I have is, there are 2 date/week columns, so a simple SUMMARIZE function will not do the job.

 

I have tried using Summarize and ADDCOLUMNS but not sure how tomake it so it only counts when the column = value of that row. See screenshot below of example where the filtering is done on an absolute value "43".

 

Here is a screenshot of the dataset (simplified).

 

 

PS. I'm quite a beginner in my PowerBI journey, so any help is appreciated 🙂

 

Not sure how I can attach an Excel file here, but hopefully the screenshots help.

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , iddy  

    Here are the steps you can refer to :

    (1)This is my test data :

     

    (2)We can click "New Table" to create a table:

     

    Table 2 = var _resolved =VALUES('Table'[week_number_resolved])
var _opened =VALUES( 'Table'[week_number_opened])
var _all =DISTINCT( UNION(_opened , _resolved))
return
ADDCOLUMNS(_all , "resolved_count" , var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_resolved]  ))  ,"opened_count" ,  var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_opened]  ))    )

     

    (3)Then we can meet your need , tghe result is as follows:

     

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

8 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , iddy  

    Here are the steps you can refer to :

    (1)This is my test data :

     

    (2)We can click "New Table" to create a table:

     

    Table 2 = var _resolved =VALUES('Table'[week_number_resolved])
var _opened =VALUES( 'Table'[week_number_opened])
var _all =DISTINCT( UNION(_opened , _resolved))
return
ADDCOLUMNS(_all , "resolved_count" , var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_resolved]  ))  ,"opened_count" ,  var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_opened]  ))    )

     

    (3)Then we can meet your need , tghe result is as follows:

     

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • iddy's avatar
      iddy
      Frequent Visitor
      3 years ago

      Wow that is amazing! Thanks very much

    • iddy's avatar
      iddy
      Frequent Visitor
      3 years ago

      Thanks for the reply - though I'm not sure how this can be used for my problem. 

       

      The issue I'm having is not the first grouping, rather appending the next column, which is grouping the count by another column based on the row value of the newly created table. 

       

      Not sure if I can explain it well here.

    • iddy's avatar
      iddy
      Frequent Visitor
      3 years ago

      Hi, yes that looks about right based on limited data.

      Can you please share how you did it?

      • vaibhavkale570's avatar
        vaibhavkale570
        Resolver III
        3 years ago

        1. in power query editor create two reference tables each for resolved and open week number

        -remove open at and weeknumber open column from the resolved reference table

        and group by the week_number_resolved

         

         

         

        -remove resolved  and week_number_resolved column from the Open reference table and group by the week_number_opened

         

         

        2.you will get the below resulting tables

         

         

        3. now make left join of these two tables based on the basis of week number column

         

         

        4. select these option and click ok

         

         

        and this is your resulting table

         

         

        and you can disable enable load for other two columns 

        do some workaround in above steps to get your result.