Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

Sum only Values where another Column has Data

Harder than it looks:  I have this simple table like below:  I want to calculate how we're performing based on the goal in a Card.  So I need to Sum only the goal values where the Gross Adds column i...
CALCULATE
card
condition
sumx
Table
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

You seem to be using a calculated column.  Probably what you wanted to do was a measure.

 

 

  • BrianNeedsHelp's avatar
    BrianNeedsHelp
    Icon for Resolver I rankResolver I
    1 year ago

    lbendlin Hi.  I'm using a measure.  [GAGoal] is a measure, so I have to use SUMX, so I tried your formula like this and get "A function placeholder has been used in a true/false expression...":  

     

    var md=MAX('Calendar'[Calendar Date])
return CALCULATE(SUMX('Subscriber Activity',[GAGoal]),'Calendar'[Calendar Date]<=md,NOT isblank('Subscriber Activity'[Gross Adds]))

     

     I got the formula below to work in the rows.  It only shows the goal in each row where there is a corresponding entry in Gross Adds.  These add up to like 3,000. But the total at the bottom shows 93,000. ????  So when I put the measure in a card it shows the total of 93k instead of 3000.  What is going on?   

     

    SumGoalslessThanDate = 
Var GAGoalCalc = CALCULATE([GAGoal])
Return
Calculate(Sumx('Calendar',GAGoalCalc),filter('Calendar','Calendar'[Calendar Date]<=LASTNONBLANK('Calendar'[Calendar Date],[Gross Adds])&& 'Calendar'[Calendar Date]>=min('Calendar'[Calendar Date])))

     


     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      1 year ago

      Not sure what is happening in the other parts of your data model.  But I can put the measure in a card just fine.

      see attached

       

      • BrianNeedsHelp's avatar
        BrianNeedsHelp
        Icon for Resolver I rankResolver I
        1 year ago

        On your file Goal is a column whereas I have Goal as a measure.  Anyhow I'm getting this error when I use the formula you presented:   "A function placeholder has been used in a true/false expression...":  Any way around that?  Your PBIX does exactly what i need, I just don't know how to make it work in this model.