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...
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

     

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.⚡

  • roasty1980's avatar
    roasty1980
    Frequent Visitor
    6 years ago

    Sorry parry2k , I don't think I am explaining myself...

     

    My "Holiday" source table has month fields

     

    Jan |  Feb | Mar

    10      5       15

    10      5       15

    10      5       15

     

    I would like to have the data as follows:

     

    Month | No of Days

    Jan            30

    Feb           15

    Mar           45

     

    Thanks

    • Pragati11's avatar
      Pragati11
      Super User
      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

       

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        roasty1980 based on you the layout of your table, what Pragati11 suggested is the way to go, unpivot your data but you don't need to use groupby step in power query, just unpivot and load the data and use measure in the visuals to group by day. Groupby is adding an extra step and can be very expensive on large datasets and that step can be avoided.

         

        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.⚡