Matrix Visual Total
Hello, I'm working with matrix visual and I would like to hide all types of sums that exist. For the example below, I'm able to hide the sum from "abril" because this is an open hierarchy, as for the "Tri1" I can't hide its sum because it is currently closed. Inside each hierarchy line I mixing up different things so it doesn't make any sense to have a number (even if it is an average or a maximum). Is it possible to hide this value? My current definitions are shown below as well.Not quite understanding Totals In Matrices
Hello my wonderful Power BI Community, I've run into a bit of an issue I'm hoping you all can share some light on. I've looked everywhere but have failed to find anything that is remotely successful. I'm currently working on a pricing report. The report utilizes a singular fact table of invoices stored in our SQL database which is queried by PBI twice for different results. The first query is called "Base Period" which is anything with an invoice date < January 01 of current year. The other table is called "Current Period" which is anything >= January 01 of current year. I then join these two tables to a bunch of Lookup tables like customers, items, etc. on their associated keys to get various fields. For example, LU Customer joins to both base period and current period on Customer_Key. LU Item joins on Item_Key, etc. Notably I have a fiscal calendar table joined ONLY to the current period table since I want people to be able to slice invoices in the current year by period number, but still see the average price of the base period as a full year calculation. See structure below: I've then created a matrix with Company, Customer, Product Category, and Item Code in the rows. I've then created a bunch of measures to calculate various fields. I'll provide the general idea along with the measure text in red or a screenshot. Further below is a screenshot of the created matrix so you can follow along. - Average selling price (ASP) in the base period --> Average Selling Price Base Period = average('Base Period'[LocalUnitPrice]) - Average selling price (ASP) in the current period --> Average Selling Price Current Period = average('Current Period'[LocalUnitPrice]) - Price Traction P1 is the first part of the price traction $ equation. It takes the Current Period ASP and subtracts the base period ASP --> Price Traction P1 = [Average Selling Price Current Period] - [Average Selling Price Base Period] - Total quantity is simply the sum of the quantity invoiced from the current period --> Total Quantity = sum('Current Period'[QuantityInvoiced]) - Price traction $ is the Price Traction P1 multiplied by the total quantity. I've thrown in an IF to make sure that the price traction $ is 0 if either of the ASPs are blank (code in sceenshot below). You'll see further down in the screenshot of the matrix that at the item code level (1901x, 894520, etc.), this calculates correctly. However, the totals need to be the sum of these values rather than the total line P1 * total quantity. This is where the price traction fix below comes into play. - Price Traction Fix is my culmination of hours of research and trying all kinds of things, however it still doesn't work. The equation simply does a sumx based on my LU_Item[Item Code] and the price traction $ measure (see code below). This was ripped straight from an article I read and has gotten me closest to what I was trying to achieve. Now the problem arises when you look at the bottom screenshot: We can see the faulty Price Traction $ not doing what I want it to do. The price traction fix however seems to fix it until i go to the upper most total where I'm $246.53 off. If I sum 1901x and 894520 for each customer (Grainger and Airgas) I get the correct amounts, but then those amounts don't sum correctly to the company level at the top. The only other filters i have applied are: A base period year which limits the base period table to only show me the prices I want to compare to A customer filter to limit the data I'm seeing in my visual A company filter to limit the data I'm seeing in my visual I'd appreciate any help anyone is willing to give! Thank you for time and energy! Have a blessed day. Sincerely, Nick
