Power BI Exchange

Expand all | Collapse all

Exclude row amount from total in a Matrix Visual

  • 1.  Exclude row amount from total in a Matrix Visual

    Posted 13 days ago
    All,

    I'm hoping this issue will not exercise the grey matter too much.  I've had a look at past posts but cannot find anything that seems to do what I need.

    Problem:
    I have a Matrix showing Sales by Brand and have a Group (subtotal) based on Country.  The rest of the matrix is measures only.  Nothing exists on Columns in the matrix.

    For one of the Countries e.g. Australia, I need to exclude sales for a certain brand in their subtotal but still show the sales value for the brand.  A strange request but there are some reasons behind it!  I've seen various posts around using HASONEFILTER, ISFILTERED, SELECTEDVALUE etc but cannot get the result I require which is to show the amount at Brand level but exclude it from the Subtotal for a specified Country.

    Using the example below, I get the following on the left but would like to see whats on the right.

    Problem and Solution
    Thanks in advance for any help you can provide.

    Matt

    ------------------------------
    Matt Minor

    ------------------------------


  • 2.  RE: Exclude row amount from total in a Matrix Visual

    Posted 12 days ago
    Edited by Miguel Félix 12 days ago
    Hi @Matt Minor,

    Since you have a very specific exclusion try the following measure:

    Total Sales excluding = 
    IF (
        HASONEFILTER ( Table1[Brand] );
        [Total Sales];
        IF (
            SELECTEDVALUE ( Table1[Country] ) = "Australia";
            CALCULATE ( [Total Sales]; Table1[Brand] <> "Brand 3" );
            [Total Sales]
        )
    )​

    Don't know if the total values also need to exclude those specific values, but if yes this measure need to be change a little and you should add also this one.

    TotalValues Sales excluding =
    IF (
        HASONEFILTER ( Table1[Country] );
        [Total Sales excluding];
        SUMX (
            SUMMARIZE (
                ALL ( Table1[Country] );
                Table1[Country];
                "Total Sales"; [Total Sales excluding]
            );
            [Total Sales]
        )
    )
    


    I prefer to do 2 measures to have it more trackable, however if you want you can join those two in a single one:

    TotalValues Sales excluding =
    IF (
        HASONEFILTER ( Table1[Country] );
        [Total Sales excluding];
        SUMX (
            SUMMARIZE (
                ALL ( Table1[Country] );
                Table1[Country];
                "Total Sales"; IF (
                    HASONEFILTER ( Table1[Brand] );
                    [Total Sales];
                    IF (
                        SELECTEDVALUE ( Table1[Country] ) = "Australia";
                        CALCULATE ( [Total Sales]; Table1[Brand] <> "Brand 3" );
                        [Total Sales]
                    )
                )
            );
            [Total Sales]
        )
    )





    Regards,



    ------------------------------
    Miguel Félix
    Business analist
    ------------------------------



  • 3.  RE: Exclude row amount from total in a Matrix Visual

    Silver Contributor
    Posted 12 days ago
    Hi @Matt Minor

    Just to add..another DAX variation…
    Total Excluded Sales = 
    ------------------------------------
    --Exclusions
    VAR Exclusionbrand = "Brand 3"
    VAR ExclusionCountry = "Australia" 
    ------------------------------------
    --create virtual table to exclude the exclusions
    VAR rlvntable =
        ADDCOLUMNS ( Table1, "Key", Table1[Country] & Table1[Brand] ) --to create a unique key for 
    filtering out excluded values
    VAR rlvntable2 =
        FILTER ( rlvntable, [Key] <> ExclusionCountry & Exclusionbrand ) 
    ------------------------------------
    --calculate the total sales without excluded values
    VAR excludedsales =
        CALCULATE ( SUM ( Table1[Sales] ), rlvntable2 ) 
    -------------------------------------
    RETURN
        IF ( ISFILTERED ( Table1[Brand] ), SUM ( Table1[Sales] ), excludedsales )​

    ​​

    ------------------------------
    Gopa Kumar S
    ------------------------------



  • 4.  RE: Exclude row amount from total in a Matrix Visual

    Posted 12 days ago
    @Gopa Kumar Sivadasan and @Miguel Félix​​.  The formula suggestions work a treat when the columns are in the same table as the measure (see below on right hand side).  However, in my real situation, the formula works but the measure is blank in the table because the Country / Brand relationship is stored in a different table (which is joined in the model).  See below on left hand side with the example I have been using.

    Matrix Exclude Row - both versionsI can rejig the tables to move the Country / Brand to the sales measure table but I am curious - is there a way to get the measures you gave me to work in my current situation?

    Thanks

    Matt

    ------------------------------
    Matt Minor

    ------------------------------



  • 5.  RE: Exclude row amount from total in a Matrix Visual

    Posted 11 days ago
    Hi,

    How is your table setup ? How does the tables relate to each other?


    ------------------------------
    Miguel Félix
    Business analist
    ------------------------------



  • 6.  RE: Exclude row amount from total in a Matrix Visual

    Silver Contributor
    Posted 11 days ago
      |   view attached
    Hi @Matt Minor
    Please see if your data model is like the one below. If so, you can use the given DAX. Hope it helps.
    thumbnail image

    thumbnail image


    Total Excluded Sales =
    ------------------------------------
    --Exclusions
    VAR Exclusionbrand = "Brand 3"
    VAR ExclusionCountry = "Australia" 
    ------------------------------------
    --create virtual table to exclude the exclusions
    VAR rlvntable =
        ADDCOLUMNS (
            Transactions,
            "Key", RELATED ( 'Area Master'[Area] ) & RELATED ( 'Brand Master'[Brand] )
        ) --to create a unique key for filtering out excluded values
    VAR rlvntable2 =
        FILTER ( rlvntable, [Key] <> ExclusionCountry & Exclusionbrand ) 
    ------------------------------------
    --calculate the total sales without excluded values
    VAR excludedsales =
        CALCULATE ( SUM ( Transactions[Sales] ), rlvntable2 ) 
    -------------------------------------
    RETURN
        IF (
            ISFILTERED ( 'Brand Master'[Brand] ),
            SUM ( Transactions[Sales] ),
            excludedsales
        )

    PFA the pbix file also.

    ------------------------------
    Gopa Kumar S
    ------------------------------

    Attachment(s)



  • 7.  RE: Exclude row amount from total in a Matrix Visual

    Posted 10 days ago
    Edited by Matt Minor 10 days ago
    Hi @Gopa Kumar Sivadasan - thanks again for the continued support.  My model is as per below.  The matrix grouping constructs are stored in a single table separate to measures.

    The join is on Brand as a M:1 relationship from Sales Original to Sales Hierarchy.

    Example Model

    ------------------------------
    Matt Minor
    ------------------------------



  • 8.  RE: Exclude row amount from total in a Matrix Visual

    Silver Contributor
    Posted 10 days ago
    Hi @Matt Minor

    In this case, you can change the variable 'rlvntable'. Change the part 'Related (Brand Master'[Brand]) to Related(Area Master'[Brand]. Basically refer to the column in the same table instead of referring to the column in the other table.

    From your diagram, I see that you have bi-directional relationship between the sales hierarchy table and the sales original table. Generally, it is strongly recommended not to use BI- Directional filtering in the relationship due to performance issues and other un-expected results in certain calculations. If at all any need arises, you can always create the Bi-Directional relationship in the DAX for that measure.

    Please let us know if the above works for you.



    ------------------------------
    Gopa Kumar S
    ------------------------------



  • 9.  RE: Exclude row amount from total in a Matrix Visual

    Posted 7 days ago
    Thanks @Gopa Kumar Sivadasan for the continued feedback.

    To keep things simple, I moved country down to the Sales table so the standard formulas will work and its a little easier to maintain.

    Many thanks to you and the community for your help - we got there in the end...!​

    ------------------------------
    Matt Minor
    ------------------------------