Forum Discussion

LRiley's avatar
LRiley
Frequent Visitor
6 years ago
Solved

VAR/Return Measure over time

Hi,    I'm hoping someone can help with this one, I have calculated the variance between the total and each row to show the impact on the overall calculation as below  This has worked perfec...
Need Help
  • LRiley's avatar
    LRiley
    6 years ago

    Hi, I have solved the issue using all except, as below 

    NPS_Impact = 

VAR Prom =
      [NPS_PRO] - CALCULATE ( [NPS_PRO],  ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
VAR Det =
      [NPS_DET] - CALCULATE ( [NPS_DET],  ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case])) 
VAR Survey =
     [NPS Surveys] - CALCULATE ( [NPS Surveys], ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
VAR NPS =
    CALCULATE([NPS %],ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
RETURN
    IF ( [NPS %],NPS- DIVIDE ( Prom - Det, Survey ))

     

    Thanks again 

LRiley's avatar
LRiley
Frequent Visitor
6 years ago

Hi Pat,

 

Thanks for the quick reply and the suggestion, I have tried several variations of adding a date to the filter, as I thought All Selected would remove the filter across the date columns also. however, I only get the NPS score for the NPS_Impact calculation

 

NPS_Impact = 

VAR Prom =
      [NPS_PRO] - CALCULATE ( [NPS_PRO], ALLSELECTED ( 'NPS_DEEP_DIVE' ), VALUES('NPS_DEEP_DIVE'[WeekStart_Case]) ) 
VAR Det =
      [NPS_DET] - CALCULATE ( [NPS_DET], ALLSELECTED ( 'NPS_DEEP_DIVE' ) , VALUES('NPS_DEEP_DIVE'[WeekStart_Case])  )  
VAR Survey =
     [NPS Surveys] - CALCULATE ( [NPS Surveys], ALLSELECTED ( 'NPS_DEEP_DIVE') , VALUES('NPS_DEEP_DIVE'[WeekStart_Case])  )
VAR NPS =
    CALCULATE([NPS %],ALLSELECTED(NPS_DEEP_DIVE),VALUES('NPS_DEEP_DIVE'[WeekStart_Case]) )
RETURN
    IF ( [NPS %],NPS- DIVIDE ( Prom - Det, Survey ))

 

I'm sure it will be something very simple and easy to correct, I have just looked at it for too long. 

 

 

LRiley's avatar
LRiley
Frequent Visitor
6 years ago

Hi, I have solved the issue using all except, as below 

NPS_Impact = 

VAR Prom =
      [NPS_PRO] - CALCULATE ( [NPS_PRO],  ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
VAR Det =
      [NPS_DET] - CALCULATE ( [NPS_DET],  ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case])) 
VAR Survey =
     [NPS Surveys] - CALCULATE ( [NPS Surveys], ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
VAR NPS =
    CALCULATE([NPS %],ALLEXCEPT( 'NPS_DEEP_DIVE',NPS_DEEP_DIVE[WeekStart_Case]))
RETURN
    IF ( [NPS %],NPS- DIVIDE ( Prom - Det, Survey ))

 

Thanks again