How to calculate percentage difference across columns in a matrix?
Questions Using DAX, how do you calculate the percentage difference across columns in a Power BI matrix? Can DAX do this without hardcoding the column value? Use Case For example, say, I want to create a matrix of Sum of Revenue against Week as Rows and Year as columns in a matrix table. See Example Data below. I want to compute percentage difference of Sum of Revenue by Year (column) for each Week (row). See Expected Result below. How could I compute that percentage difference without hardcoding Year? My data source always gives year-to-date (YTD) data for the last 3 years of data. And I am hoping to avoid having to update the hardcoded year when the new year roles over. See Background below if interested in seeing how this is done using a PivotTable in Excel. Example Data Week Year Revenue 1 2020 6800 2 2020 7300 3 2020 7350 1 2021 7260 2 2021 8300 3 2021 8360 1 2022 8300 2 2022 9100 3 2022 8900 Expected Result Week 2020 2021 2022 1 6.76% 14.33% 2 13.70% 9.64% 3 13.74% 6.46% Total 11.52% 9.95% Background I am an Excel user familiar with PivotTables. To do the same, I would pivot Sum of Revenue on Week as Rows and Year as Columns. Then change the field values of Sum of Revenue to Show Values As to % Difference From for Year as the base field and (previous) as the base item. I am trying to translate this logic in an Excel PivotTable to a Power BI matrix and am getting stuck. It appears that Power BI doesn't have the equivalent menu options. So I am guessing I have to use DAX to get this done. Excel PivotTable Menu Options Power BI Matrix Menu Options
One to Many Relation show unrelated rows
Hello Community, I have two tables with a one to many relation. After creating the relationship in the data model, I created a pivot table. There is a measure to count the rows of the many side. Now I want to add a measure, to show the rows from the one side of the relation were have no relation to the second table. I would be thankful for any advice. Kind regards
Calculating/Dividing Two Pivot Tables
I am using excel to create pivot tables and than divide the tableIs to get desired outcome. See: https://drive.google.com/file/d/1jL4vNdBxAi0LPigy3iaQ-iGFzHiMPaBA/view?usp=sharing and https://drive.google.com/file/d/1dwPDuuOju7nbLyDVa5njvLObCqviJa3Z/view?usp=sharing These questions: https://drive.google.com/file/d/1dwPDuuOju7nbLyDVa5njvLObCqviJa3Z/view?usp=sharing i am answering in Excel currently. I would like to answer them in Powerbi. Please assist? SQL i am using in Powerbi, to pull in the desired tables: # App Filter Categories select a.label as 'App Category' , b.label as 'Menu' , c.product_uid as 'Product UID' from app_menu_filters a left join app_menu_filters b on b.parent_uid=a.uid left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid where a.status='A' and a.level=1 order by 1,2,3; # Sales and Product Data SELECT date(o.created_datetime) as Date, od.product_uid as 'Product UID', p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as 'Unit of Measure', ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as 'Units Sold', ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as 'Sales Volume', ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as 'Sales Value' FROM order_detail od left join `order` o on od.order_uid=o.uid left join product p on od.product_uid=p.uid where o.status in ('D',1,2,3,4,5) and not od.label ='Plastic Bag' and date(o.created_datetime) >= '2020-04-01' Please see data in access: https://drive.google.com/file/d/1zqSbAvESHf5WUw4U-OKXjL_9E8NbJTfd/view?usp=sharing Please see data in excel: https://drive.google.com/drive/folders/1jUFHVlGZsHNdE6H0TuB2FI6c19Up52eR?usp=sharing
Percentage Format from Data Model not reflecting in pivot table
Hi there I have set up a data model from which I have created a pivot table. I have fields where the data type format in the data model is percentage. This format reflects in the field filter drop down but displays as decimal in the pivot table column label. See images from pivot table and then in the data model: How do I fix this? Thanks
