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

[Samwise23]

Hello everyone,

I would like to achieve the following table format:

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

[Anonymous]

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.

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

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!

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.

[Samwise23]

Hi @Anonymous 

See example below.

Number	Score	Created
TSK1	4	12/15/2023 13:31
TSK2	4	12/14/2023 9:15
TSK3	4	12/22/2023 10:44
TSK4	4	12/26/2023 13:34
TSK5	4	12/30/2023 10:38
TSK6	4	12/8/2023 20:24
TSK7	3	12/5/2023 13:37
TSK8	3	12/17/2023 10:34
TSK9	2	12/2/2023 10:41
TSK10	1	12/3/2023 12:09
TSK11	4	1/25/2024 11:04
TSK12	4	1/30/2024 7:51
TSK13	4	1/7/2024 15:06
TSK14	4	1/5/2024 13:13
TSK15	4	1/4/2024 13:20
TSK16	4	1/6/2024 9:08
TSK17	4	1/9/2024 16:20
TSK18	4	1/20/2024 12:06
TSK19	4	1/1/2024 9:33
TSK20	4	1/17/2024 9:41
TSK21	3	1/9/2024 11:49
TSK22	3	1/16/2024 11:01
TSK23	3	1/31/2024 12:16
TSK24	3	1/18/2024 10:49
TSK25	2	1/10/2024 15:14
TSK26	2	1/30/2024 9:49
TSK27	1	1/28/2024 12:35
TSK28	1	1/22/2024 8:55

Below is the desired Matrix in Power BI, 

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

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

[Anonymous · Accepted solution]

Hi  @Samwise23 ,

I created some data:

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

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]

2. Connecting the relationship between two tables.

3. Result:

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

[Samwise23]

Hi @Anonymous,

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?