cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ansa_naz Member
Member

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

Hi all

 

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

 

Matrix style.jpg

 

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:

 

Matrix PBI current.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@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.
1.PNG2.PNG3.PNG

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")


0.PNG

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.
Capture.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
mnayar Established Member
Established Member

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

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 Member
Member

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

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

Moderator v-yuezhe-msft
Moderator

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

@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.
1.PNG2.PNG3.PNG

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")


0.PNG

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.
Capture.PNG

Regards,
Lydia

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

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

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!