SUMX giving zero total for a column in Table visual
I am looking at a table visual where the line totals are correct but the Grand Total is coming back as zero despite using SUMX The measure is as follows PreviousMonthValue = VAR _currentOpp = SELECTEDVALUE(Opportunities_ME[OpportunityNumber]) VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM]) VAR _prevMonthEnd = EOMONTH(_currentDate, -1) VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) ) VAR _ValueLM = CALCULATE( SUMX( FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ),Opportunities_ME[EstimatedValueBase]) )+0 RETURN _ValueLM Anyone know why?
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/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 5Total Is Correct but row is not when using ALL
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?
SUMX with Calculated() - Weighted average can't be calculated
Hello all, so for some context, i'm trying to calculate a forecast accuracy with weighted average, meaning when it's all summed up, i want a value that is influenced by how much weight each row has. For the calculation i need to access different key figures, which are defined in a single row called "key figure". For the calculation of the Forecast accuracy i can just use some calculate formulas and it works perfectly fine. Problem is, when i try to make the normal forecast accuracy weighted. I tried to do this with SUMX, but it somehow gives me values that are not possible. I put much time in research and couldn't find anything. I assume that with the SUMX, some filter context of the defined variables get lost (not sure though). Anyway, here's the original DAX formula for the forecast accuracy (not weighted yet). it works as intended: result of this formula. on single row basis this works, but the total (yellow) is not weighted, which is what i try to achieve: So here is the new dax formula, that i created to get the forecast accuracy to be weighted: The result is not possible. On single row bases, the value need to be identical to the above ones, but they aren't. The total doesn't make any sense at all: I was pretty confident that the formula makes sense, but i'm probably missing something. Is this entire thing not possible, because the key figures are in one single colums, just like the values (volume cs) are in the same column? Thanks! 😃 Best regards Nicolas
SUM & SUMX causing different results, how do I resolve
I have a measure for Forecasted_at_completion within my datamodel. I noticed that the figures were right in the columns of my Matrix visualisation but when I looked at the totals they were incorrect. When I use the sum function within my 'FORECASTEDTOCOMPLETIONWEIGHT' measure i get the correct value (0 in the example below), but when i use SUMX in 'TotalFORECASTEDTOCOMPLETIONWEIGHT' it returns 15.40. I've tried debugging with chatgpt but no joy. Please help. My Forecasted_at_completion in the below should show 49.21 FORECASTEDTOCOMPLETIONWEIGHT = IF( [SumBudgetSUPPLYWEIGHT] <> 0, MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0), 0 ) TotalFORECASTEDTOCOMPLETIONWEIGHT = SUMX( DISTINCT_LEVEL_ELEMENT_TABLE, IF( [SumBudgetSUPPLYWEIGHT] <> 0, MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0), 0 ) ) FORECASTED_AT_COMPLETION = SUMX( DISTINCT_LEVEL_ELEMENT_TABLE, [SumInvoiceWEIGHT] + [SumScheduledFORECAST] + IF( [SumBudgetSUPPLYWEIGHT] <> 0, MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0), 0 ) )Calculate in measure at column won't filter other objects
Hi! I placed this measure in "values" on a Matrix: measure = CALCULATE(sum(Fact[Totalsum]),Fact[type] = "D") But when i select anything within this matrix it will filter all objects from the model excluding Fact[type] = "D" and showing the absolute total (Measure total is selected) I also tried using SumX but didn't work: measure = SUMX(FILTER(Fact,Fact[type] = "D"),Fact[Totalsum]) Why could this be?Sum over a parent element of a given child
Greetings, I have a set of data organized like a tree (site > ug > régie > cité > batiment > logement) that represents real estate. A site is a group of UG. UG is managing various "Cité" Régie is not important here. Cité is one or more buildings Batiment is a building Logement is a single room. To each site is assigned a certain number of person working all over the site. (DDE & Postes) DDE links various "Postes" aka jobs over a "site" In the Table Postes (jobs) are defined the names and how much they're getting paid annually. A have a slicer over "Cité" to select a group of building, and taking a simple measure : count of logement_code : will give me the number of rooms all over a group of building. So far, it's quite easy. Now if i drop the "Site"'s name in a pivot table & the same measure: number of rooms. It will give me the same result as the previous pivot table. How can I have a slicer selecting a "Cité" (level 4 of my tree) that affects a pivot table that will show the level 1's name and the amount of rooms all over the level 1 ? Example: I'm sure there's a nice complex DAX formula to make it work. Anyone could help me through ? Thanks a lot ! 🙂 Nicolas
Price Effect - incorrect Sumx calculations when applying several filters from different tables
Hi everyone, I looked on the forum before posting but I coudn't get a resultat that works for my situation. I'd gladly use your help on this because I have been stuck for almost a month now. I'm trying to calculate a price effect on PowerPivot in a measure but the result isn't always correct. When displayed in a Pivot Table with only the products it gives me a result that seem to be correct. The calculation on each product row is correct, and the total is an addition of all my results. It look like this : However, whenever I want to add a filter on the customer (customer category), the total is still correct but not the detail by customer : To explain a bit further how everything is tied together, the data model has 4 tables that look like this : The relations are [Product]Product ID --> Sales[Product ID], [customer]Customer ID --> Sales[Customer_ID], and [calendar]Date --> [Sales]Date. Inside the model I have a few calculations : The first one calculate a turnover without the litigation amount (when there is no cause, there is no litigation with the customer) Gross turnover:=CALCULATE(SUM(Sales[Turnover]);Sales[Litigation cause]="") The seconde one is the same but give the Last Year result : Gross turnover_LY:=CALCULATE([Gross turnover];SAMEPERIODLASTYEAR('Calendar'[Date])) Then, I do exactly the same for the quantity : Quantity_wo_litigation:=CALCULATE(SUM(Sales[Quantity]);Sales[Litigation cause]="") Quantity_wo_litigation_LY:=CALCULATE([Quantity_wo_litigation];SAMEPERIODLASTYEAR('Calendar'[Date])) With those 4 measure, I'm able to compute the € per unit sold : €/Unit_Sold:=[Gross turnover]/[Quantity_wo_litigation] €/Unit_Sold_LY:=[Gross turnover_LY]/[Quantity_wo_litigation_LY] And in the end, here's my calculation that I have a problem with : Price effect:=SUMX(VALUES('Product'[Product_ID]);IF(OR([€/Unit_Sold]=0;[€/Unit_Sold_LY]=0);0;([€/Unit_Sold]-[€/Unit_Sold_LY])*[Quantity_wo_litigation])) I think there is probably something that I still don't really understand about Row context, filter context and context transition, That prevents me from achieving what I want ... I have to add that in the sales table, an article can be sold at 2 different customers for a different price. Thus I'm even starting to doubt that VALUES(VALUES('Product'[Product_ID])) even gives me a correct answer when I don't apply a filter on customer. Here is the link to the excel file if you want to take a closer look : Price_effect Thank you very much
How to SumX over dynamic axis ?
Hello, I'd like to create a measure that performs a SumX of an indicator according to the axes I put in my table, for example : Here the MixEffectSumX measurement is good, the total is the expected number But here the measurement only works for a specific axis (Item_Category) My DAX formula : MixEffectSumX = SUMX(VALUES(Article[Item_Category]), [MixEffect]) The idea is that I would like to replace VALUES(Article[Item_Category]) with something that contains all the axes that I put in my table dynamically. Bearing in mind that I have ten or so dimensions and a hundred or so possible axes, the idea is that if I ever add axes in the future, I won't be obliged to modify this measure. Does anyone have any clues? Thanks in advance
