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 perfectly when you select one date/period on the column 

 

when I select more than on date period in the column the calculations are no longer correct 

 

Any help would be appreciated 

 

Thanks 

Liam

 
 

 

 

 

 

 

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 

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft 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