Forum Discussion

roasty1980's avatar
roasty1980
Frequent Visitor
6 years ago
Solved

Summary table based off another table

Hi all

 

I have a table that has the months Jan to Dec as fields containing a number.  I would like to sum those month fields into a new table.  The new table would have 2 fields...

 

Month |  No of Days

Jan          500

Feb         560 

Mar         454

etc etc.

 

Thanks

Keith

Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    Hi roasty1980 ,

     

    Follow steps in order in Query Editor:

     

    Sample data considered:

     Then select all the 3 columns as unpivit them:

     

    You will end up with following:

     

    Now select your ATTRIBUTE column and then GROUP BY:

    A window appears, then provide following information:

     

    You end up with the required output:

     

    Hope this helps.

     

    Thanks,

    Pragati

     

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    roasty1980 not sure why you need a summary table but the following will get you the new table:

     

    New Summary Table = 
SUMMARIZE ( Table, Table[Month], "No of Days", SUM ( Table[NumberofDays] ) )

     

    but it will be good to know the use case of it, maybe you don't need a summary table.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • roasty1980's avatar
      roasty1980
      Frequent Visitor
      6 years ago

      Thanks for the quick reply parry2k 

       

      I have a table that holds holiday data.  I trying to creating a line graph so it shows the Month on the X-axis and the total days booked on the y-axis.  Is there a way to do this without creating a new table?

       

      Thanks

      Keith

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        roasty1980 oh yes for that you don't need a summary table, create measure:

         

        Total Days = SUM ( Table[NumberofDaysCOlumn] )

         

        add a line graph visual, put a month on the x-axis and Total Days measure on values and you will get a result. As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

        https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

        ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡