SUM of complicated DAX measure

[skanord]

Hello, 

I have created the three folowing DAX measures to use in the Matrix pictured below. 

Month_Val_EOL = 
// Calculates the sum of the Monthly value for all rows where Exp_date_YN is "yes" 
// and removes any filter on 'Fiscal.Date'
CALCULATE (
    SUM ( 'Service Contr Lines (2)'[Monthly value] ),
    'Service Contr Lines (2)'[Exp_date_YN] = "yes",
    ALL ( 'Fiscal.Date' )
)

exp_date_EOL = 
// Returns "True" if the maximum expiration date in 'Service Contr Lines (2)' is less than the fiscal date

VAR all_exp_ =
    CALCULATE (
        MAX ( 'Service Contr Lines (2)'[Contract_Expiration_Date] ),
        ALL ( 'Fiscal.Date' )
    )
VAR exp_date_ =
    all_exp_ < MAX ( 'Fiscal.Date'[Date] )
    
RETURN
    IF ( exp_date_, "True", "" )

EOL_Churn = 
// Calculates the final result based on whether the expiration date is less than the fiscal date or not
VAR Month_value =
    SUM ( 'Service Contr Lines (2)'[Monthly value] )
    
RETURN
    IF ( [exp_date_EOL] = "True", [Month_Val_EOL], Month_value )

The matrix: 

The Yellow numbers represent the values generated by the Month_Val_EOL meassure. 

My problem: 

I would like to calculate the sum of all the yellow numbers, and be able to filter them by fiscal year, and other elements that are in the matrix (such as contract no, buyer, etc.). 

I am expecting to show the sum in a seperate table, but if it is possible i would prefer to sum it in the Matrix pictured above, but this might be too complicated. 

Any help is appriciated!

[MFelix]

Hi @skanord ,

Depending on the model you need to use a SUMX function can be something similar to:

TotalMonthVAl_EOL =

SUMX(VALUES(Table:_Contract_Expiration_Date), [Month_Val_EOL])

Again this depends on your model and the way you are doing your calculations.

[skanord]

I have made the folowing DAX measure: 

TotalMonthVAl_EOL = 

SUMX(VALUES('Service Contr Lines (2)'), [Month_Val_EOL])

But it only shows the sum for one month, and not for all the months highlighted in yellow, as seen in the picture below.

In the picture i have selected the line where the monthly value is 4220, and the card on the right shows the TotalMonthVAl_EOL calculation. Here the desired result would have been 29540, instead of 4220. 

How can i change the measure to achieve this?

[MFelix]

Hi @skanord ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.