Forum Discussion

dg123456789's avatar
dg123456789
Advocate III
2 years ago
Solved

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 -> Man...
Need Help
  • dg123456789's avatar
    dg123456789
    2 years ago

    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. 

parry2k's avatar
parry2k
Super User
2 years ago

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.

  • dg123456789's avatar
    dg123456789
    Advocate III
    2 years ago

    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?