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
How to change the data source of a model and run the existing queries?
I created a model based on excel file. after connecting to the data source (which is the his excel file) I created several steps and measures. Now I realized that it could be better to used a pivot table (which is in anotehr excel file) as a data source, instead of the first excel file. The layout is the same in both excel file and pivot table file (1st and 2nd file). When I change the data source to the pivot table excel file, it gives me error and I cannot connect it properly nor use the steps that I created beforehand. Error presented when trying to load new data source: "The key don't match any rows in the table". How to make that? Thanks, Diego
Pivoting a column and aggregating text values - Power Query
I'm using Power Query in Excel and I'm trying to transform the data below in the image below. This image is a snipper of a larger dataset and shows information for 2 film titles. The data I receive is laid out incorrectly, I want to pivot the 'Fields' column so that it becomes my header row, which I can do using 'Pivot Column', my problem is that I want the 'Value' information to then be listed beneath this. For example, my first column would become Title, and the first two row values for this column should be The Rig and Good Luck To You, Leo Grande. I do not know though how to get the transform to aggregate the data like this and list the values as individual rows under the pivoted column headers. Advice would be much apprecaited.Require Color Coding For Pivot Table in Matrix Visual (Power BI Server - May 2020)
Hi, Can someone please help me to color code the entire row based on the text value for "Bench category" even if the value is empty or null. Right now in BI, I have obtained below output: But the final output should look like this as shown below: Thank you in advance for the help.
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
