Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have table which contains data at date level (date granularity) i.e there is one column named "Date" and fact values are at date level, ProductId level.
Now user want a report in table format and they want one column per month and after every 3 months they want one colum to show Quarter data for those months so like the format following. how to achieve this because I never came across such requirement ?
Jan Feb March Q1 April May June Q2 July Aug Sep Q3 Oct Nov Dec Q4
Solved! Go to Solution.
Hi @Anonymous,
In source data table, add two calculated columns.
Month = FORMAT('Monthly Sales'[Date],"MMM") Quarter = "Q"&'Monthly Sales'[Date].[QuarterNo]
Enter below data manually. (In my test, it's 'Order Table').
Create a calculated table.
Table = UNION ( SUMMARIZE ( 'Monthly Sales', 'Monthly Sales'[Month], "Sum Qty", SUM ( 'Monthly Sales'[Qty] ) ), SUMMARIZE ( 'Monthly Sales', 'Monthly Sales'[Quarter], "Sum Qty", SUM ( 'Monthly Sales'[Qty] ) ) )
Establish a relationship between 'Order Table' and the calculated table 'Table'.
On report page, use a Matrix to display data.
Best regards,
Yuliana Gu
Hi @Anonymous,
In source data table, add two calculated columns.
Month = FORMAT('Monthly Sales'[Date],"MMM") Quarter = "Q"&'Monthly Sales'[Date].[QuarterNo]
Enter below data manually. (In my test, it's 'Order Table').
Create a calculated table.
Table = UNION ( SUMMARIZE ( 'Monthly Sales', 'Monthly Sales'[Month], "Sum Qty", SUM ( 'Monthly Sales'[Qty] ) ), SUMMARIZE ( 'Monthly Sales', 'Monthly Sales'[Quarter], "Sum Qty", SUM ( 'Monthly Sales'[Qty] ) ) )
Establish a relationship between 'Order Table' and the calculated table 'Table'.
On report page, use a Matrix to display data.
Best regards,
Yuliana Gu
First try to make a Calendar table using date from the data table:
1-Click on Edit Query button and it will open the Power Query
2 - Duplicate the Data Table (Right botton over the data table and duplicate)3 - Rename to Calendar and click on top menu choose columns and selec only date column
4 - click on top menu Remove Lines > Remove All duplicated Lines too garantee only on value per date.
5 - Now Select on top menu the Add Columns TAB > Date > Year > Year
6- Repeat this Click on Column Date Again and after Add Column Tab > Date > Month > Month
7 - Column Date Again and after Add Column Tab > Date > Month > Name of Month
The idea here is to get something like that:
Now you have this Calendar Table you can close and Apply on top menu TAB Base. returning to Report view and exiting from Power Query
Go into Relationships and drag and drop date field from Calendar to Data Table to make a relationship
Now go to Report View and create a new measure: Sum Sales = SUM(Data_table[Sales])
After created the measure, now you just need to select Table view in view menu and the fields and mesure as showed below:
If you want to Sort the Month name do the fallow:
Click on the field Month Name (just one click on right menu) > Modelation > Sort by Column >
Month.
I Hope i've helped
Thanks for your reply !!
But I not am looking for months on every row instead I want table view wherein each month needs to be shown as column like the following one;
Product Jan Feb March Q1 April May June Q2 July Aug Sep Q3 Oct Nov Dec Q4
so I can show Products as one column and then column named "Jan", Feb etc
Hello, aniruddha20 could you provide some part of the data table and say what if you want total or a porcentage or a single table showing Products sold by month and after products sold in first Quarter and Q2, Q3, Q4.
I've got the idea Table containing Columns Jan Feb Mar Q1 April May Jun Q2... but tell us Sell of products per month
avarage, quantity? So tell us.
Thanks for your reply !!
Data looks like following wherein 1st column is Date column and second is Sales and I want to show sum of sales for each month
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |