Forum Discussion
Limiting Rows in Matrix Table is not Subtotaling
Ok, so what I'm really after is the "Show grand totals for rows" feature that is in Excel but I need it in PowerBI.
In my matrix table I have 3 items in my hierarchy: Division -> Category -> Manufacturer
I want to limit the manufacturers shown because only the top 5 matter really.
So I followed this here to try to get it to work: Solved: Hide rows in matrix table without affecting subtot... - Microsoft Fabric Community
At first, I tried it with a RANK option to truly return the top 5 items with this DAX
Top 5 Total Sales =
VAR ManufacturerRank = RANKX(ALL('Product'[Manufacturer]), [Total Dollar Sales], , DESC)
RETURN
IF(
ManufacturerRank <= 5
, [Total Dollar Sales]
, BLANK()
)
For the most part, this worked except that my data is additive up from an item level so the Rank was giving me some weird results showing a lot more than 5 Manufacturers by category and it was showing some with low dollar amounts and excluding others with high dollar amounts.
So I pivoted and created a dataflow excel file with a list of explicit Manufacturers that I want displayed per category.
I created a relationship between this new table and the exsiting product table based on the Category columns, then I created this new measure:
Total Dollar Sales (Filtered) =
IF(
SELECTEDVALUE('Product'[Manufacturer]) IN VALUES('Category Competing Manufacturers'[Manufacturer])
, [Total Dollar Sales]
, BLANK()
)
Everything about this measure works exactly how I want it to except there's no subtotal.
I basically want the first column to subtotal like the second column is doing
This was the closest answer to get me to figure it out.
The actual answer is
VAR included_manufacturer = if( AND( SELECTEDVALUE('Product'[Manufacturer]) in VALUES('Category Competing Manufacturers'[Manufacturer]), SELECTEDVALUE('Product'[Category]) IN VALUES('Category Competing Manufacturers'[Category])) , 1 , BLANK() ) RETURN IF( sumx('Product',[included_manufacturer]) > 0 , [Total Dollar Sales] , BLANK() )But yes, the trick is for it to roll up to the subtotals we need it to be a sum, or some calculation that allows the subtotal lines to still calculate their totals.
So this final formula gives the included manufacturers a value of 1 and all other manufacturers a blank value. Then anywhere the sum of the included manufacturers is greater than 0 we can insert the Total Dollar Sales, so this means that the logic statement is TRUE for both the included manufacturers as well as the higher subtotal levels that could have sums of 2 or more.
11 Replies
dg123456789 try this:
Total Dollar Sales (Filtered) = SUMX ( FILTER ( VALUES ( 'Product'[Manufacturer], 'Product'[Manufacturer] IN VALUES('Category Competing Manufacturers'[Manufacturer]) ) , [Total Dollar Sales] )You missed a close paranthesis, but when I do this I get a "Visual has exceeded the available resources." error.
This was the closest answer to get me to figure it out.
The actual answer is
VAR included_manufacturer = if( AND( SELECTEDVALUE('Product'[Manufacturer]) in VALUES('Category Competing Manufacturers'[Manufacturer]), SELECTEDVALUE('Product'[Category]) IN VALUES('Category Competing Manufacturers'[Category])) , 1 , BLANK() ) RETURN IF( sumx('Product',[included_manufacturer]) > 0 , [Total Dollar Sales] , BLANK() )But yes, the trick is for it to roll up to the subtotals we need it to be a sum, or some calculation that allows the subtotal lines to still calculate their totals.
So this final formula gives the included manufacturers a value of 1 and all other manufacturers a blank value. Then anywhere the sum of the included manufacturers is greater than 0 we can insert the Total Dollar Sales, so this means that the logic statement is TRUE for both the included manufacturers as well as the higher subtotal levels that could have sums of 2 or more.
are you able to provide some sample data?
My model is massive and setup with a fairly involved star schema, would take me the better part of a day or two to put together a sample pbix.
What I'm really after is why two measures, seemingly utilizing the same if statement functionality, are not both giving me the same results?
dg123456789 quick question, why you are checking IN VALUES, in another table?
2nd, the reason why it is not giving you the total at the higher level because you are using selectedvalue condition which is returning blank at non manufacturer level.
I'm trying to limit the rows of the Manufactures that are displayed in the Matrix table. I'm using "IN VALUES" to select the limited group, otherwise there are 100 manufacturers some of whom earn very little money and aren't worth clogging up my report.
If you compare my two measures, they are both using a logic statement and returning a BLANK when False, but the first measure gives the subtotal and the second one does NOT.
I'm trying to understand why it works one way but not the other?
dg123456789 you should set up a relationship between these two tables which I think will be 1 to 1 and then use that logic in the DAX to only include manufacturer that are in the 2nd table.
As I said, SELECTEDVALUE does not give you the same value at non manufacturing rows, it gives you a blank value. Those are two different measures.
parry2k it is not 1:1 because some manufacturers produce products in several categories. I have a relationship between category columns as stated in my original post. So if I'm in the DETERGENT Category, then the list of manufacturers is limited to just those in that category.
So if SELECTEDVALUE() is not the way, and i've tried MAX() as well, how do I do this?
dg123456789 I see, maybe it will be easier if you put sample data in Excel file, you don't need full blown model, just relevant tables to this particular question.
- product table
- category manufacture table
and I guess there will be a fact table that calculates the dollar amount and has a relationship with the product table on the product id.
And the relationship between these tables.
I have a sample pbix file that I've created, but I cannot attach it here, and as I am on a work machine the option I have is a github repo to host a file publically. Can you see it here: dghenkel/subtotal_test (github.com)
