Trying to format my matrix visual to match an Excel report - cant figure it out!

[ansa_naz]

Hi all

I have the below Excel report which I need to recreate in PBI:

It shows a Completion Date for each lift at each site. I have added Site Name and Lift Reference into 'Rows' in the matrix visual in PBI, Completion Date I added into Columns section, and I added Completion Date into Values section. However, this gives me the below:

Anyone know how I can do the following:

- Edit the Completion Date display format to dd/mm/yyyy

- Change column names to display as mmm-yy (eg Jan-2018)

- How to display SiteName and Lift References at the same time in the same way as the Excel report, next to each other in separate columns

Many thanks for all help

[Anonymous]

To change the column names I think you would need to change the default format of the field in the modelling tab. Same thing applies for completion date.

for the layout, check out this post and it should help you get teh required layout.

https://community.powerbi.com/t5/Desktop/Is-it-possible-to-change-the-layout-of-the-new-matrix-to-lo...

[ansa_naz]

Hi Mnayar

The date is already formatted to dd/mm/yyyy, but in the matrix visual it is showing as attached previously?

I have tried turning off and on the stepped layout but it makes no change to the visual?

Cheers for the help

[v-yuezhe-msft · Accepted solution]

@ansa_naz,

1. In order to display dd/mm/yyyy format, you would need to change region settings in the computer that installing Power BI Desktop.


2. Create a new column using one of the DAX below and drag it to Column  of the Matrix visual.

New Completion Date = FORMAT('Sales volume'[Completion Date],"MMM-YYYY")

New Completion Date = FORMAT('Sales volume'[Completion Date],"MMM-YY")

3. Use the highlighted button to drill down the Matrix visual and turn off stepped layout , you will get same result as that in Excel.


Regards,
Lydia

[ansa_naz]

Thank you so much, I didnt realise I could change the date format in the visual Modelling tab!

And also, the drill all down and then turning off stepped layout - PERFECT!