Issue: Row Totals Are Correct, But Column Total is Incorrect in DAX Measure (Must Use Measures)

Question

I have a dynamic pricing model in Power BI that calculates per-quantity charges based on different asset types (Equities, Futures, and Options) and their respective rate tiers.The measure must be dynamic because users need to test different price points by adjusting the price per quantity through slicers or parameters. This means I cannot use calculated columns or pre-aggregated tables—I must use measures.What’s Working:The row-level values are correct when displaying the results.Users can interact with the report and adjust pricing dynamically.What’s Not Working:The column total does not sum correctly when all asset types and contract types are included together.If an account has multiple contract types (e.g., both Micro and Standard Futures or both Index and Non-Index Options), the total is incorrect.The measure works fine when using page-level filters (e.g., only showing one contract type), but fails when aggregating all contract types together.The total only displays correctly when a filter is applied to the visual. Without a filter, the column total does not match the sum of the row totals.What I Have Tried:Using SUMX to iterate through each row.Applying CROSSJOIN and SUMMARIZE to structure calculations properly.Breaking calculations into separate measures for each contract type (Micro, Standard, Index, Non-Index) and summing them together.Using ALLSELECTED and ALLEXCEPT to control total row behavior.Trying HASONEVALUE to adjust logic for totals.Requirement:✅ The solution must use measures (not calculated columns) because pricing needs to be dynamic.✅ The column total must correctly sum the row-level values without requiring page-level filters.✅ The report must work when multiple contract types are selected together (e.g., Micro + Standard).✅ The solution should be optimized for performance since the dataset is large.Question:How can I modify my Dynamic_Per_Quantity_Charge measure so that:The column total correctly sums the row totals, even when multiple contract types are included.The measure remains fully dynamic, allowing users to test different price points in real time.The total works correctly even when no filters are applied to the visual.Performance remains optimized for large datasets.Would greatly appreciate any insights on resolving this.&nbsp;

kjmts5200 · Answer

Here is my daxNew_Per_Quantity_Charge =SUMX(&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; CROSSJOIN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES(bowi_net_trade_revenue[AccountID]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES(bowi_net_trade_revenue[Contract Type]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Calendar'[Date])&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; "CalculatedCharge",&nbsp; &nbsp; &nbsp; &nbsp; VAR CurrentTier = [Dynamic_Tier]&nbsp; &nbsp; &nbsp; &nbsp; VAR ContractType = MAX(bowi_net_trade_revenue[Contract Type])&nbsp; &nbsp; &nbsp; &nbsp; VAR Quantity_Shares = [Quantity-Shares]&nbsp; &nbsp; &nbsp; &nbsp; VAR Quantity_Standard = [Quantity-Standard]&nbsp; &nbsp; &nbsp; &nbsp; VAR Quantity_Micro = [Quantity-Micro]&nbsp; &nbsp; &nbsp; &nbsp; VAR Quantity_Index = [Quantity-Index]&nbsp; &nbsp; &nbsp; &nbsp; VAR Quantity_NonIndex = [Quantity-Non-Index]&nbsp; &nbsp; &nbsp; &nbsp; -- Determine the correct charge based on asset type and tier&nbsp; &nbsp; &nbsp; &nbsp; VAR Per_Quantity_Charge = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- **Equities**&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Equities_Tier_1"), MAX(Per_Quantity_Charge_Tier_1_Equities[Per_Quantity_Charge_Tier_1_Equities]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Equities_Tier_2"), MAX(Per_Quantity_Charge_Tier_2_Equities[Per_Quantity_Charge_Tier_2_Equities]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Equities_Tier_3"), MAX(Per_Quantity_Charge_Tier_3_Equities[Per_Quantity_Charge_Tier_3_Equities]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Equities_Tier_4"), MAX(Per_Quantity_Charge_Tier_4_Equities[Per_Quantity_Charge_Tier_4_Equities]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Equities_Tier_5"), MAX(Per_Quantity_Charge_Tier_5_Equities[Per_Quantity_Charge_Tier_5_Equities]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- **Futures - Micro**&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_1") &amp;&amp; ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_1_Futures_Micro[Per_Quantity_Charge_Tier_1_Futures_Micro]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_2") &amp;&amp; ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_2_Futures_Micro[Per_Quantity_Charge_Tier_2_Futures_Micro]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_3") &amp;&amp; ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_3_Futures_Micro[Per_Quantity_Charge_Tier_3_Futures_Micro]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_4") &amp;&amp; ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_4_Futures_Micro[Per_Quantity_Charge_Tier_4_Futures_Micro]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_5") &amp;&amp; ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_5_Futures_Micro[Per_Quantity_Charge_Tier_5_Futures_Micro]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- **Futures - Standard**&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_1") &amp;&amp; ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_1_Futures_Standard[Per_Quantity_Charge_Tier_1_Futures_Standard]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_2") &amp;&amp; ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_2_Futures_Standard[Per_Quantity_Charge_Tier_2_Futures_Standard]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_3") &amp;&amp; ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_3_Futures_Standard[Per_Quantity_Charge_Tier_3_Futures_Standard]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_4") &amp;&amp; ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_4_Futures_Standard[Per_Quantity_Charge_Tier_4_Futures_Standard]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures_Tier_5") &amp;&amp; ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_5_Futures_Standard[Per_Quantity_Charge_Tier_5_Futures_Standard]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- **Options - Index**&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_1") &amp;&amp; ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_1_Options_Index[Per_Quantity_Charge_Tier_1_Options_Index]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_2") &amp;&amp; ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_2_Options_Index[Per_Quantity_Charge_Tier_2_Options_Index]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_3") &amp;&amp; ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_3_Options_Index[Per_Quantity_Charge_Tier_3_Options_Index]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_4") &amp;&amp; ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_4_Options_Index[Per_Quantity_Charge_Tier_4_Options_Index]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_5") &amp;&amp; ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_5_Options_Index[Per_Quantity_Charge_Tier_5_Options_Index]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- **Options - Non-Index**&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_1") &amp;&amp; ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_1_Options_NonIndex[Per_Quantity_Charge_Tier_1_Options_NonIndex]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_2") &amp;&amp; ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_2_Options_NonIndex[Per_Quantity_Charge_Tier_2_Options_NonIndex]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_3") &amp;&amp; ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_3_Options_NonIndex[Per_Quantity_Charge_Tier_3_Options_NonIndex]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_4") &amp;&amp; ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_4_Options_NonIndex[Per_Quantity_Charge_Tier_4_Options_NonIndex]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options_Tier_5") &amp;&amp; ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_5_Options_NonIndex[Per_Quantity_Charge_Tier_5_Options_NonIndex]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; -- Apply Charge ONLY to Relevant Quantity Type&nbsp; &nbsp; &nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK(Per_Quantity_Charge),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Per_Quantity_Charge * &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Equities"), Quantity_Shares,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures") &amp;&amp; ContractType = "Standard", Quantity_Standard,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Futures") &amp;&amp; ContractType = "Micro", Quantity_Micro,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options") &amp;&amp; ContractType = "Options_Index", Quantity_Index,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CONTAINSSTRING(CurrentTier, "Options") &amp;&amp; ContractType = "Options_NonIndex", Quantity_NonIndex,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; [CalculatedCharge] // The expression for SUMX to evaluate for each row)

greg_deckler · Answer

kjmts5200&nbsp;First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742eThis looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Also: https://youtu.be/uXRriTN0cfYAnd: https://youtu.be/n4TYhF2ARe8

anonymous · Answer

Hi kjmts5200,
&nbsp;
DAX doesn’t aggregate totals in matrix visuals the same way it does for individual rows. When calculating totals, it uses a broader filter context and skips row-by-row evaluation, which breaks logic relying on fine-grained granularity—especially with virtual tables like CROSSJOIN.
&nbsp;
To fix this, avoid CROSSJOIN and MAX, as they cause context transition issues. Instead, use SUMX over a SUMMARIZE table that matches your row granularity (e.g., AccountID, Contract Type, Date) to ensure consistent and accurate calculation for both rows and totals.
&nbsp;
Regards,
Vinay Pabbu

anonymous · Answer

Hi kjmts5200,
&nbsp;
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
&nbsp;
Regards,Vinay Pabbu

anonymous · Answer

Hi&nbsp;@kjmts5200,
&nbsp;
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
&nbsp;
Regards,Vinay Pabbu

Forum Discussion

Issue: Row Totals Are Correct, But Column Total is Incorrect in DAX Measure (Must Use Measures)

What’s Working:

What’s Not Working:

What I Have Tried:

Requirement:

Question:

6 Replies

Related content

Measure returning incorrect value

Measure Total Incorrect

DAX Measure Returning Incorrect Results

Correcting Data

Measure Total not correct