Total sum of a column calcuated NOT CORRECT (using function TOTALYTD; SAMEPERIODLASTYEAR: etc.)

[Tamofiore]

Hi PBI Community,

I'm new with PBI, and I need an help to fix an issue I have, and that I'm not able to solve.

I'm creating an analysis that calculate the:

Sales current year: SalesCY= TOTALYTD(sum(Consolidated[Sales]); DATE[Date])

Sales previous year: SalesPY = CALCULATE(Consolidated[SalesCY]; SAMEPERIODLASTYEAR(DATE[Date]))

then I have calculated the quantity with the same functions used for the sales:

Quantity current year: QuantityCY= TOTALYTD(sum(Consolidated[Quantity]); DATE[Date])

Quantity preiovus year: QuantityPY = CALCULATE(Consolidated[QuantityCY]; SAMEPERIODLASTYEAR(DATE[Date]))

At this point I have calculated the Price unit, divinding the

Price unit CY = Sales CY / Quantity CY

Price unit PY = Sales PY / Quantity PY

and then

Delta Price unit = Price unit CY - Price unit PY

Mix = Delta Price unit * Quantity CY

I'm arriving to the problem: If I create a Table using a Matrice that has in row

Customer / Part number and in Columns the measure "Mix", what it can be noticed is that the calculation at Part number level is correct, but the total of the column "Mix" is "wrong" , as the screen that folllows:

The final sum is 40.474 insted of 36.451. In grey is the calculation of the Mix measure at Part numer level. In blank the sum that should be at the customer level. 

How can I fix it? Why does it work wrong?

Thanks in advance for the help!

[v-jiascu-msft]

Hi @Tamofiore,

Why should it be 36.451? Just see from the data, I guess it should be -516.995. The cause could be the context. The context of the Total is the whole model if there aren't any slicers or filters. For example, the contexts of Total are the last two years. Please check out.

Best Regards,

Dale

[Tamofiore]

Hi Dale,

thanks for your reply.

The database behind the report has data since

January 2016 to June 2018.

The dashboard has 2 Filters (Year; Month).

And what I have showed is the result of the selection

Year: 2017-2018

Month: Jan - Jun 

I have tried to select the full year Jan-Dec but the Total of the column continues to be different if comapred to the real sum.

Thanks ever so much.

BR, TamoF