Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
BrianNeedsHelp
Helper III
Helper III

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 
16 REPLIES 16
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1736749616170.png

 

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. 

This is what it does:  [SumGoalslessThanDate] is like your Measure2.  

Screenshot 2025-01-13 140851.png

 

Hi @BrianNeedsHelp ,

 

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?
Please provide me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Neeko Tang

@v-tangjie-msft  Company won't allow file sharing, and the model is so large, I'm not even sure how to replicate it.  I'll try with the Contoso database and see if I can replicate the issue.  As I've explained, I think the problem may be that [GAGoal] is computed by a measure and not a column. And the [GAGoal] measure is computed by using other measures,   which is like this: 

[PreviousYearGrossAdds]/[PYGAsAll]*[LocQuota]

Did you see how it puts the grand total in each row instead of filtering it?   

Hi @BrianNeedsHelp ,

 

You can refer to my pbix file, I am also using the [GAGoal] measure instead of the columns, but it outputs correctly in my example file. Could you check your model relationships? In my sample pbix file the model relationship is unidirectional one-to-many.

vtangjiemsft_1-1736848682289.png

 

vtangjiemsft_0-1736848607021.png

 

Other than that, I think the incorrect measure results might be related to your hierarchy, could you create a table visual object and then place only [Date Column of Date Table], [GAGoal measure], [Gross Adds Column] and [SumGoals measure] and then observe the results.

vtangjiemsft_2-1736848780452.png

vtangjiemsft_3-1736848810954.png

To test this, create a new page and then only these three visual objects exist (slicer, card, table)

 

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. 

@v-tangjie-msft I took your pbix and replicated all of the measures to compute the Goals etc.  I was able to make everything work in the pbix, only summing the values in the goal less than Max Gross Add Date.   However, when I apply the same steps to my model, it simply won't work-it sums the whole total like in the picture I sent.   Tried your suggestion with table and those fields and same result.  Have no idea-it should work!  Thanks for the suggestions.   

Hi @BrianNeedsHelp ,

 

Has your issue been resolved? If not, it may be difficult to troubleshoot if you can't provide a pbix file since measures are affected by many factors. Can you provide an example pbix file? (Only example data exists, not actual data)

 

Best Regards,

Neeko Tang

@v-tangjie-msft I sent you a message yesterday that I was not able to replicate the issue in a PBIX file.  It only doesn't work in the main model I use.  

lbendlin
Super User
Super User

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

 

lbendlin_0-1736386505705.png

 

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

lbendlin_0-1736388485395.png

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. 

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.

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.   

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.