My Matrix Table Keeps Showing More than X rows despite Measures - How to fix?
Hi - Currently my table (named Overview) is displaying Sites (via distinct site table) and Customers (via distinct customer table) as the Matrix Table's rows and the overall store sales + top 5 customer sales (depending on the time period picked). Data sheet hyperlink PBIX file hyperlink I'm experiencing a problem where: 1. I can't seem to make a Top/Bottom 20 stores slicer (based off sales) 2. Adding Date columns to my Matrix Table rows removes my top 5 Customers row condition and instead shows 6 or more customer rows/exceeds query resources To illustrate what i'm doing currently - I made the table's sales value by these measures: Total Sales = SUM( Overview[Sales]) Then i used this measure as the Matrix Table value: MEASURE-SALES = IF( ISINSCOPE( 'Unique Customers'[Customer] ), [Total Sales], CALCULATE( [Total Sales], REMOVEFILTERS( 'Unique Customers'[Customer] ) ) ) As mentioned before - i also made the table show each store's top 5 customers via this rank: Customer Rank = VAR BaseTable = ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ), REMOVEFILTERS( 'Unique Customers' ) ), "@val", [Total Sales] ) VAR Result = RANK( SKIP, BaseTable, ORDERBY( [@val], DESC ), PARTITIONBY( 'Unique Stores'[Store] ) ) RETURN Result then i set this measure as = 1 in the table's filter Customer is visible = VAR CustomerRank = [Customer Rank] VAR Result = IF( ( CustomerRank <= 5 && NOT ISBLANK( CustomerRank ) ) || NOT ISINSCOPE( 'Unique Customers'[Customer] ), 1 ) RETURN Result My date table is below for reference (capped at current month for YOY measure purposes not mentioned above): Date = ADDCOLUMNS ( CALENDAR ( DATE (2023, 1, 1), DATE (2025, 4, 30) ), "Year", YEAR([Date]), "Year-Month", FORMAT([Date], "yyyy-MM"), "Year-Month sort", EOMONTH([Date], 0) ) Would like if there was a test file sample on this if possible as i keep getting stuck/written measures i recieve return an error- the common problems i'm running into for 2. as mentioned before is: - Query exceeds resource when Year-Month is added - Rows exceed 5 customers when "Year" column is added / time range on slicer is picked
Filtering
Hello. I need help about using two date slicers in one table. I have 2 table. First table include date, product, quantity. Second table include date and id. So I want to show all product and their relevant sum of quantities on matrix table with 2 date selection. But problem is that when I select first date and then second date, it's only shows products selected at first date. I'll appreciate your help. P.S. Screenshots atached.
Display last 5 weeks based on Selected Date
Hi everyone, I am pretty new to Power Bi and DAX, so I'd appreciate any pointers here. Here is my problem: Based on the selected date, I need to display data in a matrix table on the selected date and 5 weeks ago. Unfortunately, this is a problem from work, so I'm unable to share the actual data but here is an example with some dummy data: My selected date is 9/15/23. This is the data that should be displayed: If I select the date to be 9/8/23, this is how the new selected data should be displayed: In essence, selecting 9/8/23 results in the 9/8/23 totals to be added to the 9/15/23 totals. The remaining data results would display weekly from 8/11/23 up to 9/1/23. This type of functionality should exist for any selected date I choose. Here is a dummy view of what my actual table looks like. I also have a independent date table that is the distinct updated date from my actual table. Updated Date Days Open Status Count Date Difference (Today - Updated Date) 9/15/23 0 to 9 A 3 6 9/15/23 10 to 19 A 13 6 9/15/23 0 to 9 B 5 6 9/15/23 10 to 19 B 10 6 9/8/23 0 to 9 A 5 13 9/8/23 10 to 19 A 3 13 9/8/23 0 to 9 B 6 13 9/8/23 10 to 19 B 5 13 Currently, I have a display similar to the 1st picture, but this is without selecting any dates. I calculated a date difference between the updated date and Today's date and used those numbers as a filter to help display something similar to the first picture. Now if I try to select a date using a filter, it will only display that selected date's data, which isn't what I want. I tried to utilize the independent date table in the filter but obviously was running into issues because there's no actual relationship between my actual table and the date table. And even if I'm able to figure out this issue, I'm not even sure how I can visualize the second picture. Is this type of visualization even possible in Power BI? I just don't know where to begin 😞Standard Deviation from 4 measured columns.... DARN!
Hi all! I've researched so many sites (and previous answers) concerning computing standard deviation in PowerBI. I never knew it would be so difficult for me to do since Excel makes it SIMPLE! I have a matrix table that I want to resemble an excel table. The data shown are all aggregate values compiled from individual records (with some duplicates). All columns are measures. It is important for the pivot to remain and that I only calculate the current month for each year. For an example, I want to calculate March for the current year and March for the previous 5 years (2022 - 2017). While the STDEVX.P function is fairly simple, I need it to only reference my previous 5 year columns (2017-2021). When I use the function, it references EVERYTHING since I don't have a DAX formula to add in the caveats. If my vision is possible, please help me out or let me know of alternatives. I will give kudos and I don't mind reading recommendations!
Matrix Table Total Issues
Hi all, looking for some guidance, I have a matrix table visulisation which links two tables. The link seems to work fine. Table 1 : Dates Function - Week Number and End of Month Table 2: Imports - Week Number and Sales I am then showing Sales by End of month. The purpose of this is I want to build in like for like with Sales this time last year but before I get to this point I've realised the Total of my matrix table doesn't match the value of the lines within. I've read about Sum and SumX but I can't get the outcome I'm expecting. I've also tried merging the tables using Lookupvalue to create a new column so everything is in one place. Visualisation below for reference purposes. Anyone able to advise? Thanks in advance. Regards, ChrisFilter out but grand total should not be affected.
I have an original table as below - but I want to filter out countries with participants less than 5. Hence when i apply filter I get the following matrix - However, I want total as 13 only. How do I make my "total" in the matrix universal (should not be affected by filter) but at the same time, I should be able to filter out specific countries ?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
How to put calculated columns in the rows of a matrix
I’m having trouble to create a matrix table that contains columns calculated. I have a data which appears as following : | User | Col 1 | Col2 | Col3 --------------------------------------- | 1 | 20 | 5 | 10 | 2 | 4 | 50 | 44 | 3 | 4 | 4 | 49 Col1, Col2 and Col3 are calculated columns Desired matrix is the following: name Total Col1 28 Col2 59 Col3 103 Please, any help? Thank's in advance
