Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Biggest puzzler yet: I have this measure called [%Need]. The sum total of these percentages needs to be 1 in order to compute correctly. However, it's not, so in order to correct it I need to do a formula like: [%Need]/Sum [%Need] *1. So in order to get sum portion I did:
CALCULATE(SUMX(VALUES('Calendar'[calendar date]),[%Need]),ALL('Calendar'[CalendarDate]))
This puts 1(I checked it out to 15 0s 1.00000000000000 in each row instead of the correct amount which is 1.0147. 1.0147 however appears in the Total at the bottom. I need 1.0147 in each row so I can have it divided by [%Need]. This probably doesn't need to be included, but just in case the [%Need] measure is:
IF([CALendarDATE]>LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds]),[PYGA]/([PYALLGAs]-[GAsLyMAXDateAll]),Blank())
Any idea how to get the correct total in each row?
Hi @BrianNeedsHelp,
The issue arises because DAX treats row context and filter context differently when calculating totals versus individual rows. Here's a breakdown of what's happening and how to fix it:
You want each row to reflect the correct percentage by dividing the row's %Need value by the sum total (1.0147). To achieve this:
[%Need Adjusted] = VAR TotalNeed = CALCULATE(SUMX(VALUES('Calendar'[Calendar Date]), [%Need]), ALL('Calendar')) RETURN DIVIDE([%Need], TotalNeed)
CALCULATE with ALL:
Row-Level Context:
DIVIDE for Safety:
Add a temporary measure to verify the TotalNeed:
TotalNeed = CALCULATE(SUMX(VALUES('Calendar'[Calendar Date]), [%Need]), ALL('Calendar'))
This should return 1.0147 consistently across all rows.
Test the %Need Adjusted measure in your table visual.
Let me know if this resolves the issue or if you need further clarification!
Please mark this as solution if it helps you. Appreciate Kudos
Thanks for your reply and explanation. However that measure does the same thing. 1 in all the rows and 1.0147 in the total.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |