Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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/2025 | 5 | 6 |
1/2/2025 | 7 | 5 |
1/3/2025 | 8 | 7 |
1/4/2025 | 7 | 7 |
1/5/2025 | 4 | 3 |
1/6/2025 | 3 | 2 |
1/7/2025 | 2 | 1 |
1/8/2025 | 5 | |
1/9/2025 | 5 | |
1/10/2025 | 5 |
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.
This is what it does: [SumGoalslessThanDate] is like your Measure2.
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.
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.
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.
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])))
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |