Forum Discussion

Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago
Solved

Only Total Select Values in a Matrix & Formatting Help

I'm trying to visualize travel expense data in a tabular format.  I wish to group this data first by Department (Unit Section in example), then by Unit (Unit Name in example), and then order within t...
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    So I've managed to figure out a solution to my problem, and I'm sharing in case anybody else wants to only show specific totals or subtotals in a matrix.

     

    It took some digging, but I was able to figure out that the way Matrices calculate the totals/subtotals of a column is weird.  It doesn't just take all the values above and sum/average/find them.   It selects all the data, but filters at one level above that section's row groupings. 

    For my example, I'll assume it's calculating the Passenger Name field.  My data is grouped by Unit Section -> Unit Name -> Record Key, so for each normal entry it would filter and find the first Name that matches the Record Key. There was only one, so it displayed correctly.  Then it would do the same for the subtotal, but this time it would get a list of all names in the Unit Section and Unit Name, selecting the 'first' one and calling that a total. 

     

    The first thing I determined is that if I just put a raw text field into the Values section of a matrix, it would ALWAYS try to aggregate it in some way for the subtotal.  I was able to figure out that if you used a DAX measure, it could be aggregated differently than the basic aggregations, since it could evaluate arbitrary DAX code.  Knowing that it would have a list of values from different records, I found I could use the following to display nothing for most totals:

    PASSENGER NAME = SELECTEDVALUE('Travel Data'[Traveler],BLANK())

     

    This worked in MOST situations.  However, if all the values in a subsection were the same, or there was only one entry, it would still give me that value in the subtotal.  I took a few days away, and came back and found a way around this.

     

    I found some information that you can use HASONEFILTER() to determine if there is a total being calculated instead of an individual row.  My problem is that at the subtotal level, there were still at least 2 filters on my data, so that didn't work.  It's obvious now, but I realized that each individual row was being filtered by Unit Section, Unit Name, and Record Key while each subtotal was only being filtered by Unit Section and Unit Name. I didn't see it sooner because it looked like individual rows were being treated as one row of data as I was using unique keys per entry, but under the hood they were technically aggregations of size one.

     

    So finally, after days of fighting this, I was able to use this measure (one for each value of data) in order to only total the values I wanted:

    PASSENGER NAME = IF(ISINSCOPE('Travel Data'[Record Key]),SELECTEDVALUE('Travel Data'[Traveler]),BLANK())

    And we come to the end of the adventure.  PowerBI really needs to set up some sort of formatting option to not aggregate or total individual values in a matrix.  Supposedly the feature is being worked on, so I wholeheartedly suggest you vote for the idea here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-for-total-and-subtotals-in

     

    Good luck out there!

     

    EDIT: Updated my final query to reflect the better use of ISINSCOPE as opposed to ISFILTERED for the lowest level group.