How to get the total of column where a measure is implemented (price sales variance)

[josujimenez1994]

Hi everyone, 

I am trying to make a sale price variance analysis. But I want also to separate all those product that have been abandonned or that they are new. So I generate three effets 
Price variance
Sales variance
Non comparable effet

Here is my matrix. 
My problem the three last columns are measures 

Price variance 2 =

if(or([Price per unit]=0,[Price per unit LY]=0),0,([Price per unit]-[Price per unit LY])*[Quantity])

Quantity variance 2 =

if(or([Price per unit]=0,[Price per unit LY]=0),0,([Quantity]-[Quantity LY])*[Price per unit LY])

Non comparable effet =

IF(or([Quantity variance 2]=0,[Price variance 2]=0),[Sales €]-[Sales LY €],0)

My problem is that I would like to have the total of each colunm but Power Bi the measures also to the total. Is there anyway to fix it? 

Thanks in adavanced 🙂 

[josujimenez1994]

Thank you very much for you explanation. 
I know where the problem is, I already have tried X fonction, but I cannot reach my target. 
I am quite new with dax... 

[AilleryO]

Why not trying to make your calculation with columns instead of measure ?

[josujimenez1994]

I don't see how to use columns in this case. 
In the end what I am doing is a pivot table in order to match the same product in different period (so in different row). 
As far as I know, calculated columns make calculations in the row, but I would need the cell to lokk for one value in another row. 
But if I am wrong I would be delighted if you could explain me 🙂 

[AilleryO]

Hi,

Keep in mind that the total of your table is not the sum of above value but the calculation without the filters coming from the content of your visual . In your case it means that Produit 1, Produit 2... are filters which help your calculation to use the price and quantity of each product.

But it is not the case for the total... this explaining why you cannot get a correct calculation...

To get it you'll need to use a function iterating (row by row) your table.

All X functions (SUMX, AVERAGEX...) will create this iteration you need, but likewise function FILTER, ADDCOLUMNS.

If you want to list all iterative functions, use DAX GUIDE and filter on Iterator (using second drop down menu).

Another way of using iteration is creating a calculated column instead of a Measure, since columns are iterating by default.

Hope this help