cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Table format data

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Table format data

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

4.PNG

 

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

1.PNG

 

On report page, use a Matrix to display data.

2.PNG

 

Best regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
dujos
Frequent Visitor

Re: Table format data

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. 

Anonymous
Not applicable

Re: Table format data

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 

 

1.png

Highlighted
dujos
Frequent Visitor

Re: Table format data

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)img1.png3 - Rename to Calendar and click on top menu choose columns and selec only date columnimg2.png

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

 

img3.png

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:img4.png

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

 

Now go to Report View and create a new measure: Sum Sales = SUM(Data_table[Sales])img6.png

After created the measure, now you just need to select Table view in view menu and the fields and mesure as showed below:

 

img7.png

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.

 

 

img8.png

 

 

 I Hope i've helped

 

 

 

 

 

 

 

Anonymous
Not applicable

Re: Table format data

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

Microsoft v-yulgu-msft
Microsoft

Re: Table format data

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

4.PNG

 

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

1.PNG

 

On report page, use a Matrix to display data.

2.PNG

 

Best regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors