Forum Discussion

YYS's avatar
YYS
Regular Visitor
1 year ago
Solved

returning blank values when using SUMMARIZE and ADDCOLUMNS

 

 

 

Hello,

I am experiencing an issue with my DAX calculation in Power BI.
I have attached a sample PBIX file for your reference.

Here’s the situation:

  • I have a fact table named 'sales' and dimension tables named 'customer', 'product', and 'date'.

  • The relationships are as follows: 'sales'[CustomerKey] is related to 'customer'[CustomerKey], and 'sales'[ProductKey] is related to 'product'[ProductKey].

  • I am trying to calculate the previous year's quantity for each combination of 'customer'[Continent] and 'product'[Brand].

 

My measures are defined as below:

Q = SUM('sales'[Quantity])


Q_previous1 = CALCULATE([Q], DATEADD('date'[Date], -1, YEAR))

 

Now, I want to aggregate previous quantities by continent and brand.
I tried the following measure (Q_previous2):

 

Q_previous2 =
VAR SUM_TABLE =
    summarize(
        'sales',
        'customer'[Continent],
        'product'[Brand]
    )

VAR ADD_TABLE =
    addcolumns(SUM_TABLE,"Quantity",[Q],"PreviousQuantity",[Q_previous1])

Return
SUMX(ADD_TABLE, [PreviousQuantity])

 

However, when I use this measure in a matrix visualization, I get blank values for "PreviousQuantity" in certain cases (for example, for Australia and Litware, where Q_previous1 correctly returns 15, Q_previous2 returns blank).

 

On the other hand, when I use VALUES instead of SUMMARIZE,
as in Q_previous3 below, the results are correct:

 

Q_previous3 =

VAR ADD_TABLE =
    addcolumns(values('product'[Brand]),"Quantity",[Q],"PreviousQuantity",[Q_previous1])

Return
SUMX(ADD_TABLE, [PreviousQuantity])
 

 

My question:
Why is Q_previous2 returning blank values while Q_previous3 works fine?
How can I correctly calculate the previous year's quantity for each combination of 'customer'[Continent] and 'product'[Brand] using SUMMARIZE and ADDCOLUMNS?

Any advice or examples would be greatly appreciated.
Please refer to the attached PBIX file for details.

Thank you in advance!

 

Additional Note:
This sample file was created to simplify the question.
If I need to summarize only one column, using VALUES works fine.
However, when I need to summarize two columns, I have no choice but to use SUMMARIZE.
What I am looking for is a way to extract the previous year’s quantity from the table while summarizing two columns.

 

file download

  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi YYS 

    The underlying reason that Q_previous2 returns blank values while Q_previous3 works fine for certan combinations of Continent and Brand is that

    SUMMARIZE ( sales, customer[continent], 'product'[brand] )

    returns the distinct combinations of customer[continent] and 'product'[brand] corresponding to existing rows of sales in a given filter context.

     

    For example, for this combination of filters (i.e. filter context):

    • 'date'[Year] = 2024
    • customer[continent] = "Australia"
    • 'product'[brand] = "Litware"

    sales contains no rows (evident because [Q] returns blank for Australia/Litware in 2024).

     

    Therefore:

    1. SUMMARIZE ( sales, customer[continent], 'product'[brand] ) evaluated within the same filter context returns an empty table.
    2. SUMX over an empty table returns blank.
    3. So Q_previous2 returns blank.

    Put another way, continent/brand combinations that don't existing in 2024 do not appear in SUM_TABLE, and therefore no "previous year" value is computed for those combinations.

     

    If you do need to evaluate a particular measure at the continent/brand granularity and then sum, then some options are:

     

    1. Use CROSSJOIN and VALUES to produce the combinations of customer[continent] and 'product'[brand]:

    Q_previous2 =
VAR SUM_TABLE =
    CROSSJOIN ( VALUES ( customer[continent] ), VALUES ( 'product'[brand] ) )
VAR ADD_TABLE =
    ADDCOLUMNS ( SUM_TABLE, "Quantity", [Q], "PreviousQuantity", [Q_previous1] )
RETURN
    SUMX ( ADD_TABLE, [PreviousQuantity] )

     

    2. Use SUMMARIZECOLUMNS to both produce the combinations and add the "PreviousQuantity" column (SUMMARIZECOLUMNS previously didn't work within measures in all cases):

    Q_previous2 =
VAR ADD_TABLE =
    SUMMARIZECOLUMNS (
        'customer'[Continent],
        'product'[Brand],
        "PreviousQuantity", [Q_previous1]
    )
RETURN
    SUMX ( ADD_TABLE, [PreviousQuantity] )

     

    3. Refactor by first creating a measure that aggregates [Q] by continent/brand, and then another measure that computes this measure for the previous year:

    Q summed by product and brand =
SUMX (
    SUMMARIZE (
        sales,
        customer[Continent],
        'product'[Brand]
    ),
    [Q]
)
    Q summed by product and brand Previous
CALCULATE (
    [Q],
    DATEADD ( 'date'[Date], -1, YEAR )
)

     

    I note that the aggregation by continent/brand is not required for a simple sum of fact table values, but it could be required for a more exotic measure.

     

    Hopefully this is useful!

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi YYS 

    The underlying reason that Q_previous2 returns blank values while Q_previous3 works fine for certan combinations of Continent and Brand is that

    SUMMARIZE ( sales, customer[continent], 'product'[brand] )

    returns the distinct combinations of customer[continent] and 'product'[brand] corresponding to existing rows of sales in a given filter context.

     

    For example, for this combination of filters (i.e. filter context):

    • 'date'[Year] = 2024
    • customer[continent] = "Australia"
    • 'product'[brand] = "Litware"

    sales contains no rows (evident because [Q] returns blank for Australia/Litware in 2024).

     

    Therefore:

    1. SUMMARIZE ( sales, customer[continent], 'product'[brand] ) evaluated within the same filter context returns an empty table.
    2. SUMX over an empty table returns blank.
    3. So Q_previous2 returns blank.

    Put another way, continent/brand combinations that don't existing in 2024 do not appear in SUM_TABLE, and therefore no "previous year" value is computed for those combinations.

     

    If you do need to evaluate a particular measure at the continent/brand granularity and then sum, then some options are:

     

    1. Use CROSSJOIN and VALUES to produce the combinations of customer[continent] and 'product'[brand]:

    Q_previous2 =
VAR SUM_TABLE =
    CROSSJOIN ( VALUES ( customer[continent] ), VALUES ( 'product'[brand] ) )
VAR ADD_TABLE =
    ADDCOLUMNS ( SUM_TABLE, "Quantity", [Q], "PreviousQuantity", [Q_previous1] )
RETURN
    SUMX ( ADD_TABLE, [PreviousQuantity] )

     

    2. Use SUMMARIZECOLUMNS to both produce the combinations and add the "PreviousQuantity" column (SUMMARIZECOLUMNS previously didn't work within measures in all cases):

    Q_previous2 =
VAR ADD_TABLE =
    SUMMARIZECOLUMNS (
        'customer'[Continent],
        'product'[Brand],
        "PreviousQuantity", [Q_previous1]
    )
RETURN
    SUMX ( ADD_TABLE, [PreviousQuantity] )

     

    3. Refactor by first creating a measure that aggregates [Q] by continent/brand, and then another measure that computes this measure for the previous year:

    Q summed by product and brand =
SUMX (
    SUMMARIZE (
        sales,
        customer[Continent],
        'product'[Brand]
    ),
    [Q]
)
    Q summed by product and brand Previous
CALCULATE (
    [Q],
    DATEADD ( 'date'[Date], -1, YEAR )
)

     

    I note that the aggregation by continent/brand is not required for a simple sum of fact table values, but it could be required for a more exotic measure.

     

    Hopefully this is useful!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi YYS ,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

    OwenAuger Thank you for your quick response.

     

    YYS 

    Alongside the solution provided by the super user, you may also consider trying the following DAX measure.

    Q_previous2_mine =
    VAR SUM_TABLE =
        SUMMARIZE(
            'sales',
            'customer'[Continent],
            'product'[Brand]
        )

    RETURN
    CALCULATE(SUM(sales[Quantity]),DATEADD('date'[Date],-1,YEAR))
     

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

     

    Regards,

    B Manikanteswara Reddy

  • YYS's avatar
    YYS
    Regular Visitor
    1 year ago

    Thank you so much for your perfect solution.
    I had been struggling with this issue for weeks, and I learned a lot from your answer.
    I really appreciate your help.