topic Re: SUMX + CALCULATE not working in DAX Commands and Tips

SUMX + CALCULATE not working

Alessandro_Scal — Mon, 06 May 2024 16:36:20 GMT

Hi Everyone,

I am struggling with the following code that apparently seems quite simple:

Test sumx =

SUMX(

'Database',

CALCULATE(

SUM('Database'[Sales])/SUM('Database'[Quantity]), PREVIOUSMONTH('Calendar'[Date].[Date])

)

Basically, I'd like to compute the AVG price of the previous month, but I want also the results to be all summed up at every level (Customer/Item no./Business Unit). I know it does not make sense to sum up the AVG price, but this is part of a bigger code that I have already checked.

The formula doesn't seem to work, all I get is zeros.. but I am having an hard time figuring out why.. Here below I am attaching a snapshot of the Database I am working on.

Thank you!

Alessandro

Re: SUMX + CALCULATE not working

lbendlin — Mon, 06 May 2024 20:32:19 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: SUMX + CALCULATE not working

Alessandro_Scal — Wed, 08 May 2024 13:43:25 GMT

Thanks, for the advice.. reposting with a proper data structure..

Hi Everyone,

I am struggling with the following code that apparently seems quite simple:

Test SUMX and CALCULATE =

SUMX(

Test,

CALCULATE(

SUM(Test[Sales])/SUM(Test[Quantity]),PREVIOUSMONTH(Test[Invoice date].[Date])

)

The formula doesn't seem to work, all I get is zeros.. but I am having an hard time figuring out why..

This is the database (first 10 rows):

Item no.	Invoice date	Month	Year	Invoice no.	Business Unit	Product type	Customer name	Sales	Quantity
2661	11/12/2023	12	2023	3078	Business Unit A	Product A	Customer 1	26.681,70	23.405,00
3346	09/11/2023	8	2023	2181	Business Unit A	Product A	Customer 2	9.367,64	7.391,00
3346	28/12/2023	2	2023	420	Business Unit A	Product A	Customer 2	5.044,34	4.234,00
3397	31/12/2023	1	2023	134	Business Unit A	Product A	Customer 3	2.528,15	921,00
3372	13/11/2023	4	2023	825	Business Unit A	Product A	Customer 3	3.962,09	1.991,00
3370	09/11/2023	5	2023	1113	Business Unit A	Product A	Customer 3	4.641,39	3.049,00
3370	21/12/2023	2	2023	380	Business Unit A	Product A	Customer 3	3.909,62	2.734,00
2105	11/11/2023	5	2023	1158	Business Unit A	Product A	Customer 4	1.534,00	1.300,00
2105	15/12/2023	9	2023	2249	Business Unit A	Product A	Customer 5	3.555,20	2.020,00

This is the outcome:

Any help?!

Thank you!

Alessandro

Re: SUMX + CALCULATE not working

lbendlin — Wed, 08 May 2024 21:08:20 GMT

PREVIOUSMONTH and all the other Time "Intelligence" functions require a proper calendar table.

Re: SUMX + CALCULATE not working

Alessandro_Scal — Thu, 09 May 2024 15:36:34 GMT

Hi,

thanks for answering. I have taken your pbix file and only sliced it differently, but I keep not getting the SUMX of previous period AVG price. Please see below the screenshots, as you can see when I run the formula on current AVG price it works fine, while when I do it on previous AVG price it returns only blank values. Any clue why this is happening? Thanks!

Re: SUMX + CALCULATE not working

lbendlin — Thu, 09 May 2024 16:04:44 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: SUMX + CALCULATE not working

Alessandro_Scal — Fri, 10 May 2024 07:32:31 GMT

Here's is the pbix file and the expected output (in red).

Thanks

https://drive.google.com/file/d/19qXI6-giQpKrmzmFWLnxzsGrFYygXW4S/view?usp=sharing

Re: SUMX + CALCULATE not working

lbendlin — Fri, 10 May 2024 21:00:03 GMT

Link asks for access, please verify

Re: SUMX + CALCULATE not working

Alessandro_Scal — Mon, 13 May 2024 08:03:20 GMT

Now it should work.. thanks

Re: SUMX + CALCULATE not working

lbendlin — Mon, 13 May 2024 20:47:21 GMT

SUMX of prev Avg = CALCULATE( SUMX( 'Table',[Avg]),DATEADD('Calendar'[Date],-1,MONTH))