Forum Discussion

richbenmintz's avatar
richbenmintz
Resident Rockstar
4 years ago

 

Hi Creative_tree88 

 

roughly translated,

Activity Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Activity", [Financial_Year]<>"2099",Individual_Exam_Parts_Per_Event<>"CLDTH", NOT (Room IN {"CTSIM","CTSIM2"}))

 

 

Creative_tree88's avatar
Creative_tree88
Helper V
4 years ago

richbenmintz 

Many thanks for your help with this - I've just about got there with it, but now want to calculate an average per month, using the same type of exclusions etc...my syntax is:

 

Avg Per Month Demand = AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),
'Test Python Exam' [AD_Data]= "Demand",
'Test Python Exam'[AD_Examination_01]<>"CLDTH",
'Test Python Exam'[AD_CalYr]<>2099,
NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))
[Count of AD_Event_Key average per AD_CalYrMth])
 
This doesn't work, surprise surprise...I've managed to get it to work just using the first part i.e. 
 
Avg Per Month Demand = AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),
[Count of AD_Event_Key average per AD_CalYrMth])
 
But when I try to include the 'set analysis' part it falls apart.  Any ideas?  Really appreciate your pointers here.  Kind Rgds
  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    4 years ago

    Hi Creative_tree88 

     

    You need to wrapped the averagex inside a calculate like:

    Avg Per Month Demand = 
CALCULATE(AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),[Count of AD_Event_Key average per AD_CalYrMth]),
'Test Python Exam' [AD_Data]= "Demand",
'Test Python Exam'[AD_Examination_01]<>"CLDTH",
'Test Python Exam'[AD_CalYr]<>2099,
NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))
    • Creative_tree88's avatar
      Creative_tree88
      Helper V
      4 years ago

      richbenmintz

      Thank you so much!  So close...yet so far.  Really appreciate your help.  All the best.

    • Creative_tree88's avatar
      Creative_tree88
      Helper V
      4 years ago

      richbenmintz 

      If I wanted to compare current month, using the above 'Average Per Month' measure syntax - against last months average, how would this look?  Or against the same month, two years ago, or against a specific month such as 2019-03??

      Can PowerBI do this easily, without me needing to change the syntax every month to suit?