Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating a horizontal-style trend report

Hi all

 

I'm quite new to Power BI, and I'm currently teaching myself with the help of my background knowledge of other BI tools and Google! I'm working on a suite of reports for our management team, and one of those reports is a trend-style analysis.

 

I would like to try and create a Power BI report that uses a matrix to display the value of 2-4 key business metrics over a range of weeks. However I would like the time periods to be displayed aacross the columns, and not down the rows. The sort of thing I'm trying to create is as shown in the mock-up below:

 

In my source data I have a calendar table that contains separate columns for:

  • Date (e.g. 27/5/2019)
  • Financial Year (e.g. 2019)
  • Quarter Number (e.g. 3)
  • Week Number (e.g. 37)

This table has a one-to-many relationship with the data in the fact table, using the "Date" column.

 

Is this style of report even possible in Power BI? I cannot work out how I can set up a matrix in this way, with the three calendar elements (year, quarter, month) nested inside each other, and subtotals for each quarter and financial year. Ideally I would also like to base this report on a rolling 26 weeks, and to dynamically update based on the current week. 

 

I've had a search through topics on the forum and can't see anything that covers it, so I'd be grateful for any advice with this 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous ,

     

    Based on my test, it is unavailable to achieve your requirement in the format showed in your pictureI. To work around the issue, recommend you put column Financial Year, Quarter Number and Week Number onto Columns box of Matrix visual sequentially, then put Number of sales and Value of sales onto Values box. Enable the "Column subtotals" to get subtotal .

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    Based on my test, it is unavailable to achieve your requirement in the format showed in your pictureI. To work around the issue, recommend you put column Financial Year, Quarter Number and Week Number onto Columns box of Matrix visual sequentially, then put Number of sales and Value of sales onto Values box. Enable the "Column subtotals" to get subtotal .

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Amy

       

      Thanks for your feedback. I have nested the year, quarter and week measures on the columns of the matrix as you suggested. So I have been able drill down on this to get the dates (with subtotals) showing across the columns. I have also just been reading about the "Show on Rows" option that is available under the "Values" section. That seems to be getting me to where I want to be, as shown below:

       

       

      Obviously I have some work to do on the formatting of the column names but at least the principle is correct. Thanks for your help, Julian