Calculating the SUM of Calculated Measure

[Anonymous]

Hi,

I created calculated measure based on two columns in which one of them is a calulated measure. I am getting the SUM of that calculated measure wrong. I couldn't figure it out how to wite an expression to SUM the calculated measure using DAX even after looking into forums.
Can any one help me with an expression to find the SUM of a calculated measure.

Here is the screenshot

Here are the expressions

Price Variance = If(Or(Sum('Qry_MDF Pricing Analysis_3'[CY Qty])=0,Sum('Qry_MDF Pricing Analysis_3'[PY Qty])=0),0,[CY Price LC]-[PY Price LC])

Effective Price = ('Qry_MDF Pricing Analysis_3'[Price Variance])*SUM('Qry_MDF Pricing Analysis_3'[CY Qty])

I need to get the SUM of price variance and Effective price correct.

Regards,

James

[Anonymous · Accepted solution]

As i do not know the structure of your model I will use an example one.

The issue is due to when a measure is evaulated ina total spot it is evaulated against all the rows int hecurrent filter ocntext.

So say I have a table that has my products and a measure that gives the total sales for each product. This is a simple example so Power BI will probably by default do what I want it to however lets move past that.

For each row of the table that isnt the total the product is filtered in this context. However in the total it isnt filtered. Here we can use the function HASONEVALUE() or ISFILTERED(), this will return TRUE if we run it in the product rows and FALSE in the total. 

To use this we have a measure like follows

Product Sales =
IF ( HASONEVALUE( Table[Product] ), [Total Sales], BLANK () )

This measure will return the total sales measure where product is filtered and blank in the total.

What you want in the total is up to you however, say we want to manually calculate the total accross all products. For this SUMX is needed. What we want to do is get the total sales for each product and then add all these up.

Product Sales =
IF (
HASONEVALUE ( Table[Product] ),
[Total Sales],
SUMX ( VALUES ( Table[Product] ), [Total Sales] )
)

Now 

 SUMX ( VALUES ( Table[Product] ), [Total Sales] )

will be run at the total, this will iterate over all unique products, sum the sales for that product then add these numbers together.

Now for your scenario it will vary depending on your model and measures but hopefully you can apply the HASONEVALUE pattern to your problem.

Thanks

[akramayman]

SUMX is not working with me my case is I have a measure to calculate

CALCULATE(DIVIDE(max(salesorderdetail[extendedamount_base]),max(salesorderdetail[Duration(end-start)]),0),FILTER('Table','Table'[Date]<=Measures_table[end] && 'Table'[Date]>=Measures_table[start]),FILTER('Corporate Forecast','Corporate Forecast'[Order ID]=RELATED(Orders[ordernumber])))

the extendedamount_base/Duration(end-start) where is value Duration(end-start) is used only for the description filed and this field the level two in the matrix 

in the end I need to see the sum of those value in the first level like : 
Oct,Nov,Dec = 1666,67 and Jan,Feb-Sep=16667,67+916,67