Forum Discussion

Take2's avatar
Take2
Regular Visitor
3 years ago
Solved

SUM Only Specified Values For All Higher Matrix Levels

Hello -    Here is the link to the PBIX:  https://drive.google.com/file/d/1vdZmj0n0-JpCXnQ6kqTgxxjGW_8x-rx8/view?usp=drive_link   The following Rows exist in the below Matrix: - SalesRep Name ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Take2 
    Hi Nathan. Apologies for the late response. I really didn't have the time to look into it. Please look at the proposed solution below

    Type 2 V2 New = 
VAR Type1Exists = NOT ISBLANK ( [Type 1] )
VAR Type2AllQuarters =
    SUMX ( 
        SUMMARIZE ( 
            Data,
            SalesRep[SalesRep Name],
            Customer[Customer Name],
            'Product'[Product Name]
        ),
        CALCULATE (
            [Type 2],
            REMOVEFILTERS ( 'Calendar'[Year Quarter Number] )
        )
    )
VAR Result = 
    IF ( Type1Exists, Type2AllQuarters )
RETURN 
    Result
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Take2 
Hi Nathan. Apologies for the late response. I really didn't have the time to look into it. Please look at the proposed solution below

Type 2 V2 New = 
VAR Type1Exists = NOT ISBLANK ( [Type 1] )
VAR Type2AllQuarters =
    SUMX ( 
        SUMMARIZE ( 
            Data,
            SalesRep[SalesRep Name],
            Customer[Customer Name],
            'Product'[Product Name]
        ),
        CALCULATE (
            [Type 2],
            REMOVEFILTERS ( 'Calendar'[Year Quarter Number] )
        )
    )
VAR Result = 
    IF ( Type1Exists, Type2AllQuarters )
RETURN 
    Result
  • Take2's avatar
    Take2
    Regular Visitor
    3 years ago

    tamerj1 

     

    1) Please do not apologize for any delay.  I genuinely appreciate you getting back to me.

    2) This is great!  Thank you very much for the help!

    3) Trying to understand a bit more about your code .  From what I can see, the only difference in your code is that it adds SUMX & SUMMARIZE.

     

    NOTE: Below is the real code modified according to your instructions, which is why the var & column names are changed.  It's working beautifully, thanks to you.

     

    02 Installed Tamerj1 =

    VAR WrittenExists = [Written Zeros STAGE] > 0

    VAR InstalledAllQuarters =

        SUMX(

            SUMMARIZE(

                'FactWrittenSales STAGE',

                'DimSalesRep STAGE'[SalesPersonName],

                'DimCustomer STAGE'[CustomerName],

                'DimProduct STAGE'[PRODUCT_CODE]

            ),

            CALCULATE(

                [Installed Zeros STAGE],

                REMOVEFILTERS('DimCalendar STAGE'[FiscalQTRYearName])

            )

        )

    VAR Result =

        IF(WrittenExists, InstalledAllQuarters)

    RETURN

        Result

     

    If I perform only the SUMMARIZE by itself in Dax Studio, it returns 39,705 rows for the 3 summarized columns (SalesPersonName, CustomerName, PRODUCT_CODE).

     

    Does this mean that the SUMX iterates over all 39,705 rows, performing my CALCULATE statement for each row & then summing the total?

     

    Or does it  mean that the SUMX iterates only over the rows in the current Filter Context of the matrix (a tiny subset of the 39,705 which only contains 3 PRODUCT_CODE values in this example)?

     

    Regards,

    Nathan