Forum Discussion
Issue: Row Totals Are Correct, But Column Total is Incorrect in DAX Measure (Must Use Measures)
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 d...
Here is my dax
New_Per_Quantity_Charge =
SUMX(
ADDCOLUMNS(
CROSSJOIN(
VALUES(bowi_net_trade_revenue[AccountID]),
VALUES(bowi_net_trade_revenue[Contract Type]),
VALUES('Calendar'[Date])
),
"CalculatedCharge",
VAR CurrentTier = [Dynamic_Tier]
VAR ContractType = MAX(bowi_net_trade_revenue[Contract Type])
VAR Quantity_Shares = [Quantity-Shares]
VAR Quantity_Standard = [Quantity-Standard]
VAR Quantity_Micro = [Quantity-Micro]
VAR Quantity_Index = [Quantity-Index]
VAR Quantity_NonIndex = [Quantity-Non-Index]
-- Determine the correct charge based on asset type and tier
VAR Per_Quantity_Charge =
SWITCH(
TRUE(),
-- **Equities**
CONTAINSSTRING(CurrentTier, "Equities_Tier_1"), MAX(Per_Quantity_Charge_Tier_1_Equities[Per_Quantity_Charge_Tier_1_Equities]),
CONTAINSSTRING(CurrentTier, "Equities_Tier_2"), MAX(Per_Quantity_Charge_Tier_2_Equities[Per_Quantity_Charge_Tier_2_Equities]),
CONTAINSSTRING(CurrentTier, "Equities_Tier_3"), MAX(Per_Quantity_Charge_Tier_3_Equities[Per_Quantity_Charge_Tier_3_Equities]),
CONTAINSSTRING(CurrentTier, "Equities_Tier_4"), MAX(Per_Quantity_Charge_Tier_4_Equities[Per_Quantity_Charge_Tier_4_Equities]),
CONTAINSSTRING(CurrentTier, "Equities_Tier_5"), MAX(Per_Quantity_Charge_Tier_5_Equities[Per_Quantity_Charge_Tier_5_Equities]),
-- **Futures - Micro**
CONTAINSSTRING(CurrentTier, "Futures_Tier_1") && ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_1_Futures_Micro[Per_Quantity_Charge_Tier_1_Futures_Micro]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_2") && ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_2_Futures_Micro[Per_Quantity_Charge_Tier_2_Futures_Micro]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_3") && ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_3_Futures_Micro[Per_Quantity_Charge_Tier_3_Futures_Micro]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_4") && ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_4_Futures_Micro[Per_Quantity_Charge_Tier_4_Futures_Micro]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_5") && ContractType = "Micro", MAX(Per_Quantity_Charge_Tier_5_Futures_Micro[Per_Quantity_Charge_Tier_5_Futures_Micro]),
-- **Futures - Standard**
CONTAINSSTRING(CurrentTier, "Futures_Tier_1") && ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_1_Futures_Standard[Per_Quantity_Charge_Tier_1_Futures_Standard]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_2") && ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_2_Futures_Standard[Per_Quantity_Charge_Tier_2_Futures_Standard]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_3") && ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_3_Futures_Standard[Per_Quantity_Charge_Tier_3_Futures_Standard]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_4") && ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_4_Futures_Standard[Per_Quantity_Charge_Tier_4_Futures_Standard]),
CONTAINSSTRING(CurrentTier, "Futures_Tier_5") && ContractType = "Standard", MAX(Per_Quantity_Charge_Tier_5_Futures_Standard[Per_Quantity_Charge_Tier_5_Futures_Standard]),
-- **Options - Index**
CONTAINSSTRING(CurrentTier, "Options_Tier_1") && ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_1_Options_Index[Per_Quantity_Charge_Tier_1_Options_Index]),
CONTAINSSTRING(CurrentTier, "Options_Tier_2") && ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_2_Options_Index[Per_Quantity_Charge_Tier_2_Options_Index]),
CONTAINSSTRING(CurrentTier, "Options_Tier_3") && ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_3_Options_Index[Per_Quantity_Charge_Tier_3_Options_Index]),
CONTAINSSTRING(CurrentTier, "Options_Tier_4") && ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_4_Options_Index[Per_Quantity_Charge_Tier_4_Options_Index]),
CONTAINSSTRING(CurrentTier, "Options_Tier_5") && ContractType = "Options_Index", MAX(Per_Quantity_Charge_Tier_5_Options_Index[Per_Quantity_Charge_Tier_5_Options_Index]),
-- **Options - Non-Index**
CONTAINSSTRING(CurrentTier, "Options_Tier_1") && ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_1_Options_NonIndex[Per_Quantity_Charge_Tier_1_Options_NonIndex]),
CONTAINSSTRING(CurrentTier, "Options_Tier_2") && ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_2_Options_NonIndex[Per_Quantity_Charge_Tier_2_Options_NonIndex]),
CONTAINSSTRING(CurrentTier, "Options_Tier_3") && ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_3_Options_NonIndex[Per_Quantity_Charge_Tier_3_Options_NonIndex]),
CONTAINSSTRING(CurrentTier, "Options_Tier_4") && ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_4_Options_NonIndex[Per_Quantity_Charge_Tier_4_Options_NonIndex]),
CONTAINSSTRING(CurrentTier, "Options_Tier_5") && ContractType = "Options_NonIndex", MAX(Per_Quantity_Charge_Tier_5_Options_NonIndex[Per_Quantity_Charge_Tier_5_Options_NonIndex]),
BLANK()
)
-- Apply Charge ONLY to Relevant Quantity Type
RETURN
IF(
NOT ISBLANK(Per_Quantity_Charge),
Per_Quantity_Charge *
SWITCH(
TRUE(),
CONTAINSSTRING(CurrentTier, "Equities"), Quantity_Shares,
CONTAINSSTRING(CurrentTier, "Futures") && ContractType = "Standard", Quantity_Standard,
CONTAINSSTRING(CurrentTier, "Futures") && ContractType = "Micro", Quantity_Micro,
CONTAINSSTRING(CurrentTier, "Options") && ContractType = "Options_Index", Quantity_Index,
CONTAINSSTRING(CurrentTier, "Options") && ContractType = "Options_NonIndex", Quantity_NonIndex,
0
),
0
)
),
[CalculatedCharge] // The expression for SUMX to evaluate for each row
)
Hi kjmts5200,
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.
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.
Regards,
Vinay Pabbu