Forum Discussion

JulienZH's avatar
JulienZH
Icon for Helper II rankHelper II
4 years ago
Solved

Roll-Up total doesn't add up

Hi, I'm having a hard time figuring out why my total doesn't add up. Would anyone be able to help me figure out how to fix it ? The DAX for the total probably should have a different expression, ...
  • bcdobbs's avatar
    bcdobbs
    4 years ago

     

    Test = 
    SUMX(
    	VALUES( M_Countries[Country] ),
        
        VAR Growth_Rate =
        	CALCULATE(SUM(EFC[Value]),EFC[KPI]="NEP",ALL('Axis'), 'Axis'[Period]="EFC1")
        	- CALCULATE(SUM(EFC[Value]),EFC[KPI]="NEP",ALL('Axis'), 'Axis'[Period]="FC")
        
        VAR Assumed_Rate=
        	CALCULATE(SUM(EFC[Value]),EFC[KPI]="Assumed Rate", ALL('Axis'), 'Axis'[Period]="EFC1" )
    
        
        VAR OUE_over_NEP = 
        	DIVIDE(
        		CALCULATE(SUM(EFC[Value]),EFC[KPI]="OUE",ALL('Axis'), 'Axis'[Period]="FC" ),
        		CALCULATE(SUM(EFC[Value]),EFC[KPI]="NEP",ALL('Axis'), 'Axis'[Period]="FC" )
        		,0)
        
        VAR OUE_pct_var = 
        	CALCULATE(SUM('Variable Costs'[% OUE]))
    
        VAR ULAE_over_NEP=
        	DIVIDE(
        		CALCULATE(SUM(EFC[Value]),EFC[KPI]="ULAE",ALL('Axis'), 'Axis'[Period]="FC" ),
        		CALCULATE(SUM(EFC[Value]),EFC[KPI]="NEP",ALL('Axis'), 'Axis'[Period]="FC" )
        		,0)
        
        VAR ULAE_pct_var= CALCULATE(SUM('Variable Costs'[% ULAE]))
    
    	RETURN
    	
    	IF (
    		SELECTEDVALUE ( 'Axis'[Period] ) <> "EFC1",
    		BLANK (),
    		(Growth_Rate - Assumed_Rate) * (OUE_pct_var * OUE_over_NEP + ULAE_pct_var * ULAE_over_NEP)
    	)
    )

     


    You'd closed the SUMX to early. Hope the above now works!