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.
- wardy9123 months agoSuper User
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 agoCommunity 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.