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
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.  

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

I figured out that the total in the card  is coming from it  summing the total goal for each day that the amount appears in the the date.   7 * 13k.  Just don't know how to fix it?  Each row is correct, but it's the sum that's off. 

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

    remember that in a table visual measures are calculated twice, once for the rows and once for the row total. You can choose "MAX"  for the aggregation, or use HASONEVALUE /  ISINSCOPE  to modify the measure behavior according to where you are in the visual.

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

      I simplified the measure, and now the sum total is correct for the whole month.  But this isn't what I was wanting-Need to just add the numbers that are visible.  I don't think this works for a measure?  If you modify the PBIX to calculate GAGoal in a measure like [PreviousYearGrossAdds]/[PYGAsAll]*[LocQuota], you'Il see what I'm talking about. I've tried many different purmutations with all nearly the same result:  

      GoalLessthanDate = IF([Gross Adds]<>Blank(),( [PYGA]/[PYALLGAs])*[LocQuota1])

      I even used the exact same code that works here: Why Power BI totals might seem inaccurate - SQLBI     But they must be calculating on columns and not measures?  There would have to be a different approach to make this work.  Please help.   

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago
        If you modify the PBIX to calculate GAGoal in a measure like [PreviousYearGrossAdds]/[PYGAsAll]*[LocQuota], you'Il see what I'm talking about

        Very much not recommended.  Especially for troubleshooting you don't want to nest measures. Write your business logic out verbatim.  Only reference measures if you are confident about the result and are willing to absorb the cost of the extra context transitions.