TOTALS doesn't add up when summing data from 2 different time periods

[ironfavio]

I need to calculate the variation of sales volume of the current month vs the same period of the previous year.


At a product level the formula is working fine, but the subtotals and totals DO NOT ADD UP.


I have tried many approaches and techniques available on the web, but I couldn't find anything related to calculating a measure using data from different periods.


The formula basically is:
[ (Sales Qty 2016) - (Sales Qty 2015) ] * [ (Sales Amount 2015) / (Sales Qty 2015) ]


My DAX formula attempt is:
=(CALCULATE(sumx(Sales_Data,Sales_Data[Qty]))- ****Sales Qty 2016******


CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey])))* ****Sales Qty 2015******


(CALCULATE(sumx(Sales_Data,Sales_Data[Net Sales Inc.Rebate]),SAMEPERIODLASTYEAR(Date_Table[DateKey])) / ******Sales Amount 2015******


CALCULATE(sumx(Sales_Data,Sales_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[DateKey]))) ****Sales Qty 2015******


Any way that I can calculate the subtotal and totals a per the table below.

[TABLE="width: 515"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Qty 2016[/TD]
[TD]Qty 2015[/TD]
[TD]ASP 2015[/TD]
[TD]Var Vol[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD="align: right"]494[/TD]
[TD="align: right"]1,031[/TD]
[TD="align: right"]$142.11[/TD]
[TD="align: right"]-76,315[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD="align: right"]10,888[/TD]
[TD="align: right"]7,706[/TD]
[TD="align: right"]$428.29[/TD]
[TD="align: right"]1,362,814[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD="align: right"]331[/TD]
[TD="align: right"]1,218[/TD]
[TD="align: right"]$215.85[/TD]
[TD="align: right"]-191,462[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD="align: right"]11,720[/TD]
[TD="align: right"]9,961[/TD]
[TD="align: right"]$372.43[/TD]
[TD="align: right"]655,112[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Should be this total-->[/TD]
[TD="align: right"]1,095,037[/TD]
[/TR]
</tbody>[/TABLE]



Many thanks.
Favio

 

[FDibbins]

Note sure where those formulas come from, but I used this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

3​

[/td][td][/td][td]Qty 2016[/td][td]Qty 2015[/td][td]ASP 2015[/td][td]Var Vol[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Product A[/td][td]

494​

[/td][td]

1,031​

[/td][td]

$142.11 ​

[/td][td]

-76,315​

[/td][td]

($76,313.07)​

[/td][/tr]

[tr][td]

5​

[/td][td]Product B[/td][td]

10,888​

[/td][td]

7,706​

[/td][td]

$428.29 ​

[/td][td]

1,362,814​

[/td][td]

$1,362,818.78​

[/td][/tr]

[tr][td]

6​

[/td][td]Product C[/td][td]

331​

[/td][td]

1,218​

[/td][td]

$215.85 ​

[/td][td]

-191,462​

[/td][td]

($191,458.95)​

[/td][/tr]

[tr][td]

7​

[/td][td]TOTAL[/td][td]

11,720​

[/td][td]

9,961​

[/td][td]

$372.43 ​

[/td][td]

655,112​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td]Should be this total-->[/td][td][/td][td]

1,095,037​

[/td][td][/td][/tr]
[/table]
F4=(B4-C4)*D4
copied down.

Not sure where you got the values in row 7, but either way, you cannot calc the bottom-line total like that, you need to calc it by row, then sum the row totals

 

[ironfavio]

Hi Ford,

Thanks for your response.

The table is an example of the results I get when using the DAX formula in my example. In every row I get the correct result, but the total does not add up. This is the issue I have for my example, I would like to now if there is any trick or different formula I need to apply to get the correct result.

Regards,
Favio

 

[Matt Allington]

Read my blog post on this topic to understand what is wrong and how to fix it.
When to use SUM vs SUMX in DAX | Excelerator BI

 

[ironfavio]

Hi Matt,

Thanks your replying.

I indeed read your post a few days ago, but I wasn't able to solve my issue. I have tried several approaches but I cannot make the subtotals and totals work correctly.

I need this for my job, is there any additional tip you can give me.

Cheers,
Favio

 

[FDibbins]

In every row I get the correct result, but the total does not add up. This is the issue I have for my example, I would like to now if there is any trick or different formula I need to apply to get the correct result.

I answered that question in the last line of my post

you cannot calc the bottom-line total like that, you need to calc it by row, then sum the row totals

Each item has a different value, so while you can calc the total for each item/row going across, the same does not apply for the bottom-line total...this MUST be the sum or each row total

 

[Matt Allington]

Ironfavio

DAX is not like excel. In order to give you a correct formula, I need to know the entire data model. Every table works together and hence table names, column names, joins etc all matter. But let me try to guess. You may need to change table names and/or column names.

That basic pattern you need is

Total sales = sumx(values(sales_data[product column]),calculate(sales_data[qty]))

i dont understand what you are doing with SAMEPERIODLASTYEAR as that needs a date filter to work - you don't provide any information about the filters in your pivot. It this doesn't help, I suggest you post a sample workbook. With what you have got and show what you expect the answer to be.

 

[ironfavio]

Hi Matt,

I do not know if it was your advice or that today I may woke up more inspired but I finally did it. Many thanks anyway.

I would like to share my dax formulas in case it is useful for the people on this forum.

Basic formula

(Q2 - Q1) x P1 = Volume variance

Where: Q1 = Sales Quantity in period Y-1
Q2 = Sales Quantity in period Y
P1 = Average Selling Price in period Y-1

My DAX formulas are:

Q2:
=sumx(values(Sale_Data[Product]),CALCULATE(SUM(Sale_Data[Qty]),SAMEPERIODLASTYEAR(Date_Table[Date])))

Q1:
=sumx(values(Sale_Data[Product]),CALCULATE(SUM(Sale_Data[Qty])))

P1:
=sumx(values(Sale_Data[Product]),CALCULATE(SUM(Sale_Data[Amount]),SAMEPERIODLASTYEAR(Date_Table[Date])))/[Q1]

Variance:
=sumx(values(Sale_Data[Product]),([q2]-[Q1])*[P1])

Regards,
Favio