Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm having an issue writing a measure when I am using a selectedvalue function, a calculate function and a sum or sumx function. I'm not sure if a Summarize function needs to be incorporated.
There are 3 measures I am trying to create and they expand from the previous, so I'm thinking if I can correct the base measure, the others will function without complex DAX.
It starts with a Fee.
I then have my second measure:
Extended Fees = Fees * Quantity
I was reading on the forums that using a sumx can correct an incorrectly summing total, but it didn't help when I was using the IF statements. I also read that using SUMX ( SUMMARIZE ( can help to populate the totals and actually in the Fees I almost don't want it to populate because it's not a total of 314.03, it's just an average of 157.03 across the lines, whereas on the Invoice Amt 1 I do want a total of 10998.72
I hope this is a easy DAX structure issue, otherwise I can try to create some sample data.
or:
Appreciate the help in advance. Thank you.
Solved! Go to Solution.
@Anonymous,
This is the basic pattern I use for calculating totals. There are various ways to define the virtual table (SUMMARIZE, CALCULATETABLE, VALUES, etc.). Once the virtual table is defined, use an iterator function like SUMX to calculate the total. If you want to suppress the total for a measure, use the function HASONEVALUE to determine if a single value for a column exists in the filter context (if one value exists, display the number; otherwise, display blank).
Total Measure =
VAR vTable =
ADDCOLUMNS (
SUMMARIZE ( Table, Table[Column] ),
“@Amount”, [Base Measure]
)
VAR vResult =
SUMX ( vTable, [@Amount] )
RETURN
vResult
Here's a simpler way to write your measures:
Additional Fees =
SWITCH (
SELECTEDVALUE ( 'Budgeted Cost'[Cost Group] ),
"A",
CALCULATE (
SUM ( 'Item Price'[Price] ),
USERELATIONSHIP ( 'Item Price'[Site], Contract[Site] )
),
"B",
CALCULATE (
SUM ( 'Item Price'[Price] ),
USERELATIONSHIP ( 'Item Price'[Site], Contract[Site] )
)
)
Proud to be a Super User!
@Anonymous,
This is the basic pattern I use for calculating totals. There are various ways to define the virtual table (SUMMARIZE, CALCULATETABLE, VALUES, etc.). Once the virtual table is defined, use an iterator function like SUMX to calculate the total. If you want to suppress the total for a measure, use the function HASONEVALUE to determine if a single value for a column exists in the filter context (if one value exists, display the number; otherwise, display blank).
Total Measure =
VAR vTable =
ADDCOLUMNS (
SUMMARIZE ( Table, Table[Column] ),
“@Amount”, [Base Measure]
)
VAR vResult =
SUMX ( vTable, [@Amount] )
RETURN
vResult
Here's a simpler way to write your measures:
Additional Fees =
SWITCH (
SELECTEDVALUE ( 'Budgeted Cost'[Cost Group] ),
"A",
CALCULATE (
SUM ( 'Item Price'[Price] ),
USERELATIONSHIP ( 'Item Price'[Site], Contract[Site] )
),
"B",
CALCULATE (
SUM ( 'Item Price'[Price] ),
USERELATIONSHIP ( 'Item Price'[Site], Contract[Site] )
)
)
Proud to be a Super User!
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |