Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Kikihoffmann
New Member

Table/matrix with date in column

Hi,

 

Isn't possible to create a matix with dates/months as columns and various measures as rows?

 

Like this:

Kikihoffmann_0-1665687365815.png

 

Thank you!

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Kikihoffmann ,

According to your description, use various measures as rows can just follow @sevenhills 's solution. 

For your second question, here's my solution.

1.Bold only works for all the measures instead of a particular measure. In the visual formatting pane>Values.

vkalyjmsft_0-1666598899694.png

2.For example you want to color the Sales measure to red, in the Cell elements tab, select Sales in the Series option, then turn on the Font color option.

vkalyjmsft_1-1666599029256.png

Click the fx button, in the dialoge, select Gradient format style, select red color both in the lowest value and highest value.

vkalyjmsft_2-1666599213389.png

Get the result:

vkalyjmsft_3-1666599414854.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

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
v-yanjiang-msft
Community Support
Community Support

Hi @Kikihoffmann ,

According to your description, use various measures as rows can just follow @sevenhills 's solution. 

For your second question, here's my solution.

1.Bold only works for all the measures instead of a particular measure. In the visual formatting pane>Values.

vkalyjmsft_0-1666598899694.png

2.For example you want to color the Sales measure to red, in the Cell elements tab, select Sales in the Series option, then turn on the Font color option.

vkalyjmsft_1-1666599029256.png

Click the fx button, in the dialoge, select Gradient format style, select red color both in the lowest value and highest value.

vkalyjmsft_2-1666599213389.png

Get the result:

vkalyjmsft_3-1666599414854.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

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

Kikihoffmann
New Member

Thank you so much! 

 

A last question in this - is it possible to highlight (color/bold) some measures? 

 

E.g. if I want sales and total costs to be bold/red

sevenhills
Super User
Super User

Yes it is possible.

 

1) Build a matrix,

      Say, with Month-YY on columns
             and All the measures you want as usual i.e. in the values section

sevenhills_0-1665703537561.png

 

2) change these settings

          Go to format your visual in the visualizations

          search for "switch values to rows"

          Turn them on

sevenhills_1-1665703584628.png

 

You will be seeing the above layout

Hi @sevenhills  how do you get the Date columns to be properly sorted (chronologically)?

I did the same, but I can't find a way to sort the columns properly, it only allows me to sort them by the measures values.

Agucho76_0-1698666386187.png


It does not respect the year nor quarter nor month order of the columns. It sorts them by Revenues.

BTW, my dates table columns (month name, quarter name, and year) are properly sorted.

Tip: For any column that needs the custom sort i.e., you don't want default sort, you have to do these steps.

 

Steps:

* Select the column that you want to apply the custom sort

* Use the Column tools menu

* Select the sort by column and chose the custom right column order.

 

sevenhills_0-1698702531838.png

 

* If you dont have the custom right column order, create that one before doing the above steps and you can make this as hidden column, so the user wont see.

 

* Silly tip, but in rare cases works for few: Duplicate the column of Date and format as Month Year and use it. Rare I said, say, the data contains only first or last date of your month and when you format and use this ony it works. (you dont need sort by)

 

Please go through this links: 

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto... 

https://radacad.com/sort-column-name-in-power-query-via-m-code

https://radacad.com/sort-a-column-with-a-custom-order-in-power-bi 

https://www.biinsight.com/quick-tips-how-to-sort-matrix-by-column-in-descending-order/

https://www.youtube.com/watch?v=KK1zu4MBb-c

 

Hope this helps!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.