Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |