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!
I have tried searching for solutions, but couldn't find one that worked for my specifc use case.
I have a table like this:
orderid | orderlineid | productname | amount |
9871 | 1 | Product A | 92 |
9871 | 2 | Product X | 25 |
1536 | 1 | Product B | 150 |
1536 | 2 | Product A | 97 |
7152 | 1 | Product X | 82 |
7152 | 2 | Product X | 51 |
6128 | 1 | Product A | 41 |
6128 | 2 | Product B | 25 |
I then want to create the following measures:
1. Total order revenue for all orders containing Product X - expected result is 92+25+82+51 = 250
2. Number of orders containing Product X - expected result is 2
3. Total order revenue for all orders except orders containing Product X - expected result is 150+97+41+25 = 313
4. Number of orders except orders containing Product X - expected result is 2
I appreciate any help!
Solved! Go to Solution.
[Total] = SUM( T[Amount] )
[# Orders] = DISTINCTCOUNT( T[Order ID] )
// 1
[Total X] =
var OrdersWithX =
calculatetable(
distinct( T[Order ID] ),
keepfilters (
T[Product] = "Product X"
)
)
return
calculate(
[Total],
OrdersWithX
)
// 2
[# Orders X] =
CALCULATE(
[# Orders],
KEEPFILTERS(
T[Product Name] = "Product X"
)
)
// 3
[Total ~X] = [Total] - [Total X]
// 4
[# Orders ~X] = [# Orders] - [# Orders X]
[Total] = SUM( T[Amount] )
[# Orders] = DISTINCTCOUNT( T[Order ID] )
// 1
[Total X] =
var OrdersWithX =
calculatetable(
distinct( T[Order ID] ),
keepfilters (
T[Product] = "Product X"
)
)
return
calculate(
[Total],
OrdersWithX
)
// 2
[# Orders X] =
CALCULATE(
[# Orders],
KEEPFILTERS(
T[Product Name] = "Product X"
)
)
// 3
[Total ~X] = [Total] - [Total X]
// 4
[# Orders ~X] = [# Orders] - [# Orders X]
That worked like a charm! Thanks!
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 |
---|---|
46 | |
26 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |