Matrix visual - values seem to not be displaying correctly

[Anonymous]

Hi all,

I have two tables, Work Item and Forecast, on a One to Many relationship. Using the table visual I have the following subset:

Work Item Id	Forecast Id	Amount $	Forecast Type	Financial Year	Financial Year Quarter
1539	3349	0.00	Type A	FY 19/20	Q4
1539	3350	4500.00	Type B	FY 19/20	Q4
1539	3431	1.00	Type C	FY 19/20	Q4
1540	3347	2000.00	Type A	FY 20/21	Q1
1540	3348	4500.00	Type B	FY 20/21	Q1
1540	3432	1.00	Type C	FY 20/21	Q1

The four columns to the right belong to the Forecast table and Work Item Id is from the parent. Financial Year and Financial Year Quarter are in a hierarchy structure in my fields pane.

I want to display these in a Matrix with Work Item and Forecast in the Rows and the Financial Year and Financial Year Quarter in the Columns, with the Amount $ in the Values. It only works properly if I use one level of the Column structure at a time. If I try to expand and have the FY and FYQ at the same time in the columns, my values get messed up. Work Item 1539's values are duplicated to FY 20/21 Q1, while Work Item 1540's values disappear. Snippets below.

FY only - displays correctly     Q only - displays correctly     Both - info from 1540 disappears and info from 1539 gets replicated, confirmed by the change in the total

The sources are SharePoint lists, and they have the values set up correctly. The Power BI model also has correctly captured the source data - the Data view shows the exact same scenario I have with the table visual I described above.

I have used these Matrix configurations before and this is the first time I see this happening. Anyone can provide some help?

[amitchandak]

@Anonymous , I tried it, seems like working. Please find the file attached after signature

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.