Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Resolver 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 is not blank(GAs are in the past and Goals are for the whole month). In theory this should work, but it puts a total for all the months previous rather than what is selected in the slicer.  What's strange is if I replace [GAGoal] with [Gross Adds] in the measure below it correctly adds up the Gross Adds for the current month selected in the slicer.    

 

GoalTotalCurrent = VAR LastDaySelection =     LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds] )  VAR CurrentRange =     DATESBETWEEN ( 'Calendar'[Calendar Date], MIN ('Calendar'[Calendar Date] ), LastDaySelection )  RETURN     IF ( LastDaySelection >= MIN ( 'Calendar'[Calendar Date]),CALCULATE ( [GAGoal], CurrentRange ))

 

 I've tried things like this, but it results in blank:  

 

SumGoals = IF(SUMX('Subscriber Activity',[Gross Adds])<>Blank(),SUMX('Subscriber Activity',[GAGoal]))

 

So it shouldn't sum 1/8,1/9,or1/10

Date      Goal  Gross Adds   
1/1/202556
1/2/202575
1/3/202587
1/4/202577
1/5/202543
1/6/202532
1/7/202521
1/8/20255 
1/9/20255 
1/10/20255 
CALCULATE
card
condition
sumx
Table

16 Replies

  • lbendlin's avatar
    lbendlin
    Super 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
      Resolver 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])))

       


       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi BrianNeedsHelp ,

     

    I want to acknowledge valuable input provided by lbendlin . Their initial ideas help guide my approach. However, I noticed that more details are needed to fully understand this issue.

     

    We can create two measures.

    Measure 2 = IF(SUM('Table'[Gross Adds ])<>BLANK(),[GAGoal],0)
    SumGoals = SUMX(FILTER(ALLSELECTED('Calendar'),[Date]<=MAX('Calendar'[Date])),[Measure 2])

    Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.