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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Samwise23
Regular Visitor

Create Custom Table with Dynamic Month-Year As Column Names (column headers)

Hello everyone,

 

I would like to achieve the following table format:

Samwise23_0-1709111137014.png

 

- The measures are calculated from different tables that are not connected to each other (no relationship).

- The month names such as 'Nov-23' should be obtained from the tables where measures came from, each table has date/time column. I created a new column to extract month and year but when I added them to a Matrix in the report view, they are in alphabetical order and not chronological order, how to do it the proper way?

 

- How can I achieve the above column headers and manage to put each measure in a row, and each measure's value in its respective row and month column?

 

Any help will be much appreciated.

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

Hi  @Samwise23 ,

I created some data:

vyangliumsft_0-1709084387919.png

Power BI's default sorting is alphabetical, so the following will happen, you can create a new table to join and use the columns of the new table for sorting

vyangliumsft_1-1709084387919.png

Here are the steps you can follow:

1. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[month_year],"mindate",
    MINX(FILTER(ALL('Table'),
    'Table'[month_year]=EARLIER('Table'[month_year])),[Date]))

Select[month_year] – Column tools – Sort by column – [mindate]

vyangliumsft_2-1709084405862.png

2. Connecting the relationship between two tables.

vyangliumsft_3-1709084405863.png

3. Result:

 

vyangliumsft_4-1709084422080.png

 

Best Regards,

Liu Yang

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

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Samwise23 ,

 

Generally, columns are placed on top of the Row and the column name is automatically displayed here:

You can try the following:

Column headers - Text color - change its color to see if the column name is displayed or not.

vyangliumsft_0-1709173071196.png

Specific Columns - turn off Apply to Title to see if the rule applies to the header

vyangliumsft_1-1709173071197.png

You can try clicking Reset all setting to default to restore the matrix to its default state, but be aware that the state of the matrix will be restored in its entirety!

vyangliumsft_2-1709173090415.png

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Liu Yang

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

Hi @v-yangliu-msft 

 

See example below.

NumberScoreCreated
TSK1412/15/2023 13:31
TSK2412/14/2023 9:15
TSK3412/22/2023 10:44
TSK4412/26/2023 13:34
TSK5412/30/2023 10:38
TSK6412/8/2023 20:24
TSK7312/5/2023 13:37
TSK8312/17/2023 10:34
TSK9212/2/2023 10:41
TSK10112/3/2023 12:09
TSK1141/25/2024 11:04
TSK1241/30/2024 7:51
TSK1341/7/2024 15:06
TSK1441/5/2024 13:13
TSK1541/4/2024 13:20
TSK1641/6/2024 9:08
TSK1741/9/2024 16:20
TSK1841/20/2024 12:06
TSK1941/1/2024 9:33
TSK2041/17/2024 9:41
TSK2131/9/2024 11:49
TSK2231/16/2024 11:01
TSK2331/31/2024 12:16
TSK2431/18/2024 10:49
TSK2521/10/2024 15:14
TSK2621/30/2024 9:49
TSK2711/28/2024 12:35
TSK2811/22/2024 8:55

 

Below is the desired Matrix in Power BI, 

Samwise23_1-1709230881756.png


- The highlighted column header is what I need now, in the Matrix that first column header is not editable, can't give that column a name. See screenshot below.

Samwise23_2-1709231215416.png

 

- I created a calculated column for Month Year such as "Ded 2023" using 

month_year = FORMAT(Table[Created], "MMM yyyy")

- Weighted Average is a measure I calculated from the above Excel table and added that measure to the Matrix.

- Low Scores percentage is a measure that calculates the total amount of scores 1 and 2 for each month divided by the total of all received score for each month to get percentage of those two low scores each month. In December 2 out of 10 (20%).

 

I will add other measures later on, but for now I want to know how to format the Matrix table to look like the Excel version above (with first column having title)?

Many thanks in advance.

v-yangliu-msft
Community Support
Community Support

Hi  @Samwise23 ,

I created some data:

vyangliumsft_0-1709084387919.png

Power BI's default sorting is alphabetical, so the following will happen, you can create a new table to join and use the columns of the new table for sorting

vyangliumsft_1-1709084387919.png

Here are the steps you can follow:

1. Create calculated table.

Table 2 =
SUMMARIZE(
    'Table','Table'[month_year],"mindate",
    MINX(FILTER(ALL('Table'),
    'Table'[month_year]=EARLIER('Table'[month_year])),[Date]))

Select[month_year] – Column tools – Sort by column – [mindate]

vyangliumsft_2-1709084405862.png

2. Connecting the relationship between two tables.

vyangliumsft_3-1709084405863.png

3. Result:

 

vyangliumsft_4-1709084422080.png

 

Best Regards,

Liu Yang

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

Hi @v-yangliu-msft,

 

Thanks a lot for the solution.

 

I have just a question if you could assist with as well, the first column where I put the measures doesn't have a name, is it possible to give it a name/title?

Samwise23_1-1709132662563.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.