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.
Hi all,
I'm trying to create a measure that sums the values of a measure (1) when the values of another measure (2) is above 20.
I have tried different measures I found in the forum but none of them seems to work. (Note: Measure 1 and Measure 2 can change depending on the time bucket I apply)
e.g I tried CALCULATE( [Customers], IF ([Spend]>20)), I tried variations of SUMX and didn't get a result
Measure 1 | Measure 2 | Measure Neeeded | |
Time Bucket | Customers | Spend | |
00:00 - 00:15 | 10 | 45 | 10 |
00:15 - 00:30 | 25 | 40 | 25 |
00:30 - 00:45 | 30 | 40 | 30 |
00:45 - 01:00 | 5 | 10 | |
01:00 - 01:15 | 80 | 20 | |
01:15 - 01:30 | 90 | 10 | |
01:30 - 01:45 | 100 | 15 | |
01:45 - 02:00 | 120 | 58 | 120 |
02:00 - 02:15 | 90 | 50 | 90 |
… | … | … | … |
… | … | … | … |
Can anyone help?
Many thanks in advance
Solved! Go to Solution.
I created a _Summary_ table that has columns named [@Customers] and [@Spend] and the used SUMX to iterate over the _Summary_ table, filtering on the [@Spend] column and summing on the [@Customers] column.
This pattern works assuming [Customers] and [Spend] are columns of 'Table'. If those are actually measures instead of columns, then the DAX is slightly simpler:
BigSpenders =
VAR _Summary_ =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Time Bucket] ),
"@Customers", [Customers],
"@Spend", [Spend]
)
RETURN
SUMX ( FILTER ( _Summary_, [@Spend] > 20 ), [@Customers] )
You can create a calculated table within a measure that you then apply SUMX to like this:
BigSpenders =
VAR _Summary_ =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Time Bucket] ),
"@Customers", CALCULATE ( SUM ( 'Table'[Customers] ) ),
"@Spend", CALCULATE ( SUM ( 'Table'[Spend] ) )
)
RETURN
SUMX ( FILTER ( _Summary_, [@Spend] > 20 ), [@Customers] )
Hi @AlexisOlson
I appreciate the help.
I don't seem to be able to select the measures in the sum functions...
I believe this works with the native columns but does not seem to work with my measures (@Customers & @Spend)
I created a _Summary_ table that has columns named [@Customers] and [@Spend] and the used SUMX to iterate over the _Summary_ table, filtering on the [@Spend] column and summing on the [@Customers] column.
This pattern works assuming [Customers] and [Spend] are columns of 'Table'. If those are actually measures instead of columns, then the DAX is slightly simpler:
BigSpenders =
VAR _Summary_ =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Time Bucket] ),
"@Customers", [Customers],
"@Spend", [Spend]
)
RETURN
SUMX ( FILTER ( _Summary_, [@Spend] > 20 ), [@Customers] )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |