Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Exclude row amount from total in a Matrix Visual

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

    Posted Jan 08, 2019 06:13 AM
    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

    ------------------------------
    Power Summit (bi) - Post


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

    Silver Contributor
    Posted Jan 09, 2019 04:10 AM
    Edited by Miguel Félix Jan 09, 2019 04:53 AM
    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
    ------------------------------

    Power Summit (bi) - Post


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

    Posted Jan 09, 2019 06:32 AM
    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
    ------------------------------

    Power Summit (bi) - Post


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

    Posted Jan 09, 2019 10:09 PM
    @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

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

    Power Summit (bi) - Post


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

    Silver Contributor
    Posted Jan 10, 2019 02:26 AM
    Hi,

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


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

    Power Summit (bi) - Post


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

    Posted Jan 10, 2019 11:44 AM
      |   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)

    Power Summit (bi) - Post


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

    Posted Jan 11, 2019 12:34 AM
    Edited by Matt Minor Jan 11, 2019 03:55 AM
    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
    ------------------------------

    Power Summit (bi) - Post


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

    Posted Jan 11, 2019 04:57 AM
    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
    ------------------------------

    Power Summit (bi) - Post


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

    Posted Jan 14, 2019 05:13 PM
    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
    ------------------------------

    Power Summit (bi) - Post