Volume query with measures (dividing filtered value by total value)

[markhay]

I'm attempting to complete some price volume mix analysis and have been given a set of calculations but I'm now stuck when trying to divide a measure which will be filtered by rows by the total value of the column. The values I'm using are measures and would be displayed in a matrix with Dates as columns and Item Types as rows.

The plain text calculation would be:

Quantity - ((QuantityLY/TotalQuantityLY) * TotalQuantity)

In the scenario of 1 type of item sales the values would be as below (in a step order)

31498 – ((34087/ 1254085) * 1229307)

31498- (0.0271807732330743 * 1229307)

31498 - 33,413.51480083087

-1,915.51480083087

Any sense checks would be appreciated. 

[markhay · Accepted solution]

Resolved - 

[QuantityM] - (([QuantityLY]/CALCULATE([QuantityLY],ALLSELECTED()))*CALCULATE([QuantityM],ALLSELECTED()))

[DataNinja777]

Hi @markhay 

I am curious why QuantityLY is being substituted by two different numbers in your example.  Do you mean, the first QuantityLY is QuantityCY instead?  

Best regards,

[markhay]

Good spot - fat fingers. Should have read as: Quantity - ((QuantityLY/TotalQuantityLY) * TotalQuantity)

[PabloVallejo12]

QuantityLY meaning quantity last year?

[markhay]

Yes - the middle part aims to work out the proportion of that items sales against all sales.

i.e. what % of sales quantity was attributed to that item last year and then multiplying by current year total volume would show what sale volumes would have been expected if proportions remained the same.