Forum Discussion
Sum only Values where another Column has Data
You seem to be using a calculated column. Probably what you wanted to do was a measure.
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])))
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
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.
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.