Forum Discussion
Grouping Different columns in matrix
Hi everyone,
I am creating a report regarding a complex computation. The report was initially created in manually in excel then was required to be in our shared organization. The data is imported to a cloud platform and I have found that Power BI is the excellent candidate to create the report that the management need.
To go to my problem, I would like a help from an expert regarding grouping of columns. As you see in the image below(a report matrix) the column header is an individual columns in the table and not as a single field containing a date value. It is in fact a column name.
The structure of the table is designed this way such that instead of 12x12 of rows used per items I will only have 12 rows consumed.
Another reason for this structure is that each months pair (rule of 72 date vs column name(month)) have their own computations and not a direct value.
So, I would like to know how to group this column names as per quarter in a way that it will sum up as a group and also can drill down like as if it was a date can switch between (Qtr and month) field Like:
Qtr 1 (January, February, March)
Qtr 2 (April, May, June)
etc.
Below is how the actual raw table looks like:
Any help is greatly appreciated. Thank!
Regards,
Ray
Anonymous ,First unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
I would prefer to create a date from month like
new column in table
Date = datevalue( "01-" &[Month] & "-2023" )
in case it month year
Date = datevalue( "01-" &[Month])
Create a date table and join with that
= Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)How to create a date/calendar table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s
Use Qtr from date table on Matrix column
4 Replies
- amitchandak
Super User
Anonymous , First of all unpivot all the month columns to row. Then you can create group or a new column using switch of if
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning
Use month and month group on the column of Matrix
Hi amitchandak,
Thank you so much for your response.
I am quite confused on how to do this. I tried your suggestion and also checked the link you provided.
I tried to create a group but with no successes as it only shows the grouping of values inside the column.
I would like to create a drillable group like exactly how the date type column behaves where you can choose between Quarter display, month display, or even year display.
So it is like creating a Qtr x where members are months, then drill down to Qtr x so that per month will be displayed.
If you can give me a detailed steps on how can this be achieve would be a great help.Best regards,
Ray
- amitchandak
Anonymous ,First unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
I would prefer to create a date from month like
new column in table
Date = datevalue( "01-" &[Month] & "-2023" )
in case it month year
Date = datevalue( "01-" &[Month])
Create a date table and join with that
= Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)How to create a date/calendar table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s
Use Qtr from date table on Matrix column
Hi amitchandak ,
Your answer works perfectly!
From 504 rows, it suddenly becomes 6000+ columns! Which is something I would expect.The solution works like a charm! 🙂
Best answer!Thank you so much! I am very grateful for your help!
Regards,
Ray