Forum Discussion
Summarize table by Year
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!
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,
3 Replies
- DataNinja777
Super User
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,
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
Or do you know how to remove the gray repeating 'total' rows below:
