Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Calculation

Hello,

 

I am struggling to add a measure which will count the number of Column A values where the sum of column B grouped by A is equal to or greater than a parameter. ('Goal'[Goal Value]) and Column C = "Y".

 

My test data:

The measure should return:

      1 if the goal value = 4

      2 if the goal value = 3

      3 if the goal value = 2

 

This seems to be simple, but it has bested me today.  

 

Thanks in advance.

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Thinking:

     

    Measure = 
  VAR __Parameter = 4
  VAR __Table = 
    SUMMARIZE(
      FILTER('Table',[C] = "Y")
      [A],
      "__B",SUM([B])
    )
RETURN
  COUNTROWS(FILTER(__Table,[__B] >= __Parameter))

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Thinking:

     

    Measure = 
  VAR __Parameter = 4
  VAR __Table = 
    SUMMARIZE(
      FILTER('Table',[C] = "Y")
      [A],
      "__B",SUM([B])
    )
RETURN
  COUNTROWS(FILTER(__Table,[__B] >= __Parameter))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I copied as is and received the following errors "The syntax for '[A]' is incorrect. (DAX( VAR __Parameter = 4 VAR __Table = SUMMARIZE( FILTER('Table',[C] = "Y") [A], "__B",SUM([B]) )RETURN COUNTROWS(FILTER(__Table,[__B] >= __Parameter))))."

      PBI wanted a comma after the "...,[C] = "Y")".  But it appears to work. 

       

      Thanks.