Summarize table by Year

[adityah]

Hi Guys,

Sorry if this question seems noobish. I have the following shaped data 

policy number	product_code	lob_description	Date	GWP
x1	O	MARINE	1/1/2024	20
x2	A	HULL	1/1/2023	30
x3	A	HULL	1/1/2023	40
x4	G	DEFENSE	1/1/2024	50
x5	O	MARINE	1/1/2022	60

And I want to summarise and visualise this data based on product code and year like this:

lob_description	product_code	GWP_2022	GWP_2023	GWP_2024
MARINE	O	60	0	20
HULL	A	0	70	0
DEFENSE	G	0	0	50

I have tried the pivot table visualisation but I am unable to add an extra column(lob_description) to the primary pivot column(product code) on the left side of the pivot table.

I am trying dax but am unable to create a separate column based on year. Any help is much appreciated!! Thanks!

[adityah]

Or do you know how to remove the gray repeating 'total' rows below:

[DataNinja777 · Accepted solution]

Hi @adityah ,

You can achieve your desired output by using the Matrix visual. Start by expanding the row headers using the double arrow icon highlighted in green, then set the layout to 'Outline' as shown below.

I have attached an example pbix file.  

Best regards,

[adityah]

Thanks @DataNinja777 !!! This works but I think a DAX solution might be better since when I add a 3rd column to the left side it becomes slightly ugly