Summary table based off another table

[roasty1980]

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

[Pragati11]

Hi @roasty1980 ,

You can try using following DAX to create a table with just 2 columns selected:

Tabletest = SELECTCOLUMNS(ClothingSales, "Col1", ClothingSales[Category], "Col2", ClothingSales[Date])

For more infornation on SELCTEDCOLUMNS, see below:

https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax

Thanks,

Pragati

[parry2k]

@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]

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]

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

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]

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 · Accepted solution]

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]

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