returning blank values when using SUMMARIZE and ADDCOLUMNS

[YYS]

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

[YYS]

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.

[v-bmanikante]

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

[OwenAuger · Accepted solution]

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!