Forum Discussion
Power BI Year Over Year analysis using the matrix visual
- 3 months ago
Hi LondiweNkosi,
Thanks for reaching fabric community, You can perform below steps to calculate %chnage by selecting the slicers.
1) Relationship should be like between your sales and date table.2) Create measures
- GP = SUM ( SalesData[GP] )- GP Previous Year =CALCULATE ([GP],SAMEPERIODLASTYEAR ( 'Date'[Date] ))- GP YoY % =DIVIDE ( [GP] - [GP Previous Year], [GP Previous Year] )3) Matrix visual format should be like this
Just want to conform you need YOY % chnage is that correct?
Please consider as an accepted solution if helps or give some kudos.
sample table I createdSalesData = DATATABLE ( "Customer", STRING, "Date", DATETIME, "GP", INTEGER, { { "DTX", "2023-03-01", 1825000 }, { "DTX", "2024-03-01", 1745623 }, { "Ftech", "2023-03-01", 2325460 }, { "Ftech", "2024-03-01", 1014587 }, { "Clicks", "2023-03-01", 985625 }, { "Clicks", "2024-03-01", 1003265 }, { "Joe Soap", "2023-03-01", 114522 }, { "Joe Soap", "2024-03-01", 256341 } } )Date = ADDCOLUMNS ( CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2024, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month Name", FORMAT ( [Date], "MMM" ), "Month Number", MONTH ( [Date] ), "Year Month", FORMAT ( [Date], "MMM yyyy" ) )
wardy912 Lodha_Jaydeep Both methods are working perfectly for calculating the %change, However I only want one total column that shows the difference between the 2 years.
The picture above shows multiple columns but I only need one total column showing the difference between the years. The picture below shows the example of how the data must be visualised.
Hi LondiweNkosi
Simply remove totals from the visual using the format pane.
--------------------------------
I hope this helps, please give kudos and mark as solved if it does!
Connect with me on LinkedIn.
Subscribe to my YouTube channel for Fabric/Power Platform related content!
- LondiweNkosi3 months agoFrequent Visitor
Hi wardy912 The Total column is the one that should appear showing the overall total between the 2 years (including GP total, GP Difference total and GP percentage total). what I do not need is the GP difference and GP Percentage in between the Month Year Columns. I hope this clarifies the problem I am currently faced with.
- v-shchada-msft3 months ago
Community Support
Hi LondiweNkosi,
Thank you for posting your query in the Microsoft Fabric Community Forum.
We are sorry but this is unfortunately a limitation of the native Matrix visual. While the % Difference and GP Difference measures can be calculated correctly, Power BI will still repeat those measure columns for each Month-Year group once they are added to the Values section. Because of that, the exact layout from your example cannot be fully achieved in a single matrix visual. While You can approximate it using measures returning BLANK() for non-total columns, it will only hide values, header names are still shown. You can keep the main matrix for the Month-Year GP values and use a second aligned table/matrix visual on the right side for the overall GP Total, GP Difference and GP % Difference columns though.
Thank you.