I'm working on a Power BI project and need to create a Matrix visualization where each hour has both scheduled and actual arrival times displayed side by side, similar to the example below:

First row is hour, plan is scheduled time, fact is factual time and values are minutes. In plan column is mean value of arrivals at that scheduled time

My table has the following columns: trip_id station_id scheduled_arrival_time fact_arrival_time I've created additional calculated columns: scheduled_hour (extracted hour from scheduled_arrival_time) scheduled_arrival_minute fact_arrival_minute

I duplicated and appended tables to create a new column "Type" with values "Plan" and "Fact". I set up the Matrix visualization: Rows: scheduled_hour Columns: Type (Plan/Fact) Values: scheduled_arrival_minute and fact_arrival_minute However, Power BI automatically aggregates the values, and I cannot find a way to display them without summarization.

Using an ID to Avoid Aggregation I tried adding an ID to each arrival time and used it as a column in the Matrix. Problem: This approach resulted in gaps/empty cells in the table.

I want the Matrix table to display raw values side by side, without any aggregation or empty cells. The table should look exactly like the example above. How can I achieve this in Power BI? Any guidance or suggestions would be greatly appreciated!

I'm working on a Power BI project and need to create a Matrix visualization where each hour has both scheduled and actual arrival times displayed side by side, similar to the example below:

First row is hour, plan is scheduled time, fact is factual time and values are minutes. In plan column is mean value of arrivals at that scheduled time

My table has the following columns: trip_id station_id scheduled_arrival_time fact_arrival_time I've created additional calculated columns: scheduled_hour (extracted hour from scheduled_arrival_time) scheduled_arrival_minute fact_arrival_minute

I duplicated and appended tables to create a new column "Type" with values "Plan" and "Fact". I set up the Matrix visualization: Rows: scheduled_hour Columns: Type (Plan/Fact) Values: scheduled_arrival_minute and fact_arrival_minute However, Power BI automatically aggregates the values, and I cannot find a way to display them without summarization.

Using an ID to Avoid Aggregation I tried adding an ID to each arrival time and used it as a column in the Matrix. Problem: This approach resulted in gaps/empty cells in the table.

I want the Matrix table to display raw values side by side, without any aggregation or empty cells. The table should look exactly like the example above. How can I achieve this in Power BI? Any guidance or suggestions would be greatly appreciated!

• powerbi
• powerbi-desktop

1 Answer 1

Power BI's Matrix visual automatically aggregates data when multiple values exist for a given intersection, so you need to restructure your data to display raw, unaggregated values side by side. One approach is to use Power Query to group your data by hour and then add an index column within each group for both the scheduled ("Plan") and actual ("Fact") arrival times. You can then merge these two grouped tables on both the hour and the index so that each row contains both a scheduled and an actual arrival time. This way, each cell in your Matrix corresponds to a unique pair of values, and you won't see any unwanted aggregation or empty cells. Alternatively, if in case you don't mind showing the values as concatenated text, you can create a DAX measure using CONCATENATEX to display the list of values for each hour, though this approach converts the numbers to text. Ultimately, by ensuring your data is reshaped so each row is unique, you can have the Matrix display the raw values exactly as you want without aggregation.