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
Matrix Table format error in Power BI server after May 2024 update
Hi Community, I am having issues on the Matrix Table format after updating to May 2024. I have tons of reports with the matrix table format, and after updating the report in May 2024 Server, the Matrix table's format is distorted. The "Stepped layout" was replaced in the new update. I tried to use the Tabular format, but problem still exists. Need your help please. See below snipshot. Thank you for your help.
How to prevent a measure from being duplicated in each column when using a matrix in Power BI
Hi, hope you have a good day. Please your help with this, I have not been able to find the way to create this visual in Power BI and it seems to me quite simple. I have used the matrix where I can show the measure A for each selected month, so far so good. Now, I need to add a single column with a calculation that takes into account different months (Measure B), "similar to a total but with a specific operation, not a simple sum, average etc" when i add the value the column is duplicated by every month and the value is affected by this filter, I just need to show it at the end of the table once. This is the example in excel of what i want to do, I have looked in the customization options of the table but I can't find the way to do it. I would appreciate it if someone could help me
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, Chris
