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 

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

I would first try adding the week filter back into the calculation with VALUES('Date'[WeekColumn]) into each of your CALCULATE() expressions.

 

..., ALLSELECTED('NPS_Deep_Dive'), VALUES('Date'[WeekColumn]))  // replace with your actual week column name

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

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