Growth rate of averages using Summarize

[rob_wherewe]

I have a database with unique customers and volumes sold to them, and ultimately I want to create a growth rate based on the "average volume" per customer in a given period relative to a past period. Everything works beautifully except for one strange behavior, which relates to what happens when I use a "time intelligence" command to get the past volumes by customer. I'm going to simplify my equation somewhat because the error also repeats in this case. 

sumx ( summarize (Customer,Customer[ID],"Calc1",

CALCULATE(sum(Market[Volume]),sameperiodlastyear( Calender[Date].[Date])),

[Calc1])

So when I place that measure on a matrix table with Customer[ID] as rows and Calender[Date] as columns, it all works as expected, with the value shown being volume sold to that cutomer in the "same period last year".

The problem is the following. If a customer does not have any sales say September 2019, it returns a BLANK, even though I am expecting it to return the September 2018 number. If I do a more simple formula such as...

CALCULATE(sum(Market[Volume]),sameperiodlastyear( Calender[Date].[Date]))

....then it does return the right value. However I need to use the Summarize command in order to derive averages (for a host of reasons). 

Its driven me nuts. Any suggestions?

[sturlaws]

Hi @rob_wherewe ,

according to the DAX gurus in SQLBI, SUMMARIZE is sometimes a tricky thing to use: https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

Could you try to rewrite your code to this:

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Customer, Customer[ID] ),
        "Calc1", CALCULATE (
            SUM ( Market[Volume] ),
            SAMEPERIODLASTYEAR ( Calender[Date].[Date] )
        )
    ),
    [Calc1]
)

Cheers,
Sturla

[rob_wherewe]

Thanks so much for reply. 

I did read that Summarize sometimes produces strange behaviour. I did actually try the "AddColumns" measure (and just tried again) - it produces the same error, i.e. compared to the simple equation... 

        CALCULATE(sum(Market[Volume]),sameperiodlastyear( Calender[Date].[Date])) 

....everything matches beautifully except for CURRENT months in which the customer has no volume, it then produces a blank instead of the volume in the SamePeriodLastYear as expected and as the simple formula does. 

Is there any other way to get a "virtual table" in a measure (so I can derive the monthly averages per customer - and compare to same in SamePeriodLastYear)?

Thanks again for help.... 

[rob_wherewe · Accepted solution]

OK, after some more investigating, I did find some "solutions".

• As indicated, simply adding the AddColumns function to the measure did not change anything. 
• I also tested including a column from the Customer table that was at a higher level of aggregation than the Customer[ID], e.g. Customer[CustomerGroup] - its also a column that is not the link to other tables in the model. That also did not work. 

What did work, however, was a combination of the two above. The horrible blanks disappeared, and I was happy with that level of aggregation. 

What ALSO worked was taking the time-intelligence function out of the Summarize or AddColumns function, and rather wrapping the whole thing in a calculate, with the time-intelligence but stuck into that e.g. 

Calculate(

sumx ( summarize (Customer,Customer[ID],"Calc1",

sum(Market[Volume])),[Calc1]),

sameperiodlastyear( Calender[Date].[Date]))

If you interested, also see an answer to the same question (different emphasis) I got here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Mysterious-blanks/m-p/836812#M6233