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,
How can I add a measure to the matrix visual with unpivoted data? I already created some measures to be able to filter Month0 and Month1 as I need. But the matrix doesn't allow me to add another row after the 'Sales' row. My measure called 'SPH' should dynamically recalculate based on what values are in the 'Hours' and 'Sales' rows.
Please see the attached pbix.
Appreciate your help!
Solved! Go to Solution.
Here's what you have to do. First and foremost, never, ever use a single-table model. I mean NEVER. If you want to know what's the reason for this not-to-ever-be-violated rule, watch this eye-opening and warning video that everyone using PBI should watch (it's mandatory): Auto-exist on clusters or numbers - Unplugged #22 - YouTube
With the basics out of the way, let's focus on the task at hand. First, you've got to get your model into this shape:
Then your measures become:
DEFINE
MEASURE Data[Month1] =
var DateSelected = SELECTEDVALUE( Dates[Date] )
VAR StartMonth = EOMONTH ( DateSelected, 0 ) + 1
VAR EndMonth = EOMONTH ( DateSelected, 1 )
var SelectedAttribute = SELECTEDVALUE( Attributes[Attribute] )
return
SWITCH( TRUE(),
SelectedAttribute in {"Hours", "Sales"},
VAR Result =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= StartMonth,
Dates[Date] <= EndMonth
)
return
Result,
SelectedAttribute in {"SPH"},
VAR Sales =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= StartMonth,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Sales",
ALL( Attributes )
)
VAR Hours =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= StartMonth,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Hours",
ALL( Attributes )
)
VAR Result = DIVIDE( Sales, Hours )
RETURN
Result
)
MEASURE Data[Month0] =
var SelectedAttribute = SELECTEDVALUE( Attributes[Attribute] )
var DateSelected = SELECTEDVALUE( Dates[Date] )
var EndMonth = EOMONTH ( DateSelected, 0 )
return
SWITCH( TRUE(),
SelectedAttribute in {"Hours", "Sales"},
VAR Result =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= DateSelected,
Dates[Date] <= EndMonth
)
return
Result,
SelectedAttribute in {"SPH"},
VAR Sales =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= DateSelected,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Sales",
ALL( Attributes )
)
VAR Hours =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= DateSelected,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Hours",
ALL( Attributes )
)
VAR Result = DIVIDE( Sales, Hours )
RETURN
Result
)
And here's your Attributes table:
And the final result:
Here's what you have to do. First and foremost, never, ever use a single-table model. I mean NEVER. If you want to know what's the reason for this not-to-ever-be-violated rule, watch this eye-opening and warning video that everyone using PBI should watch (it's mandatory): Auto-exist on clusters or numbers - Unplugged #22 - YouTube
With the basics out of the way, let's focus on the task at hand. First, you've got to get your model into this shape:
Then your measures become:
DEFINE
MEASURE Data[Month1] =
var DateSelected = SELECTEDVALUE( Dates[Date] )
VAR StartMonth = EOMONTH ( DateSelected, 0 ) + 1
VAR EndMonth = EOMONTH ( DateSelected, 1 )
var SelectedAttribute = SELECTEDVALUE( Attributes[Attribute] )
return
SWITCH( TRUE(),
SelectedAttribute in {"Hours", "Sales"},
VAR Result =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= StartMonth,
Dates[Date] <= EndMonth
)
return
Result,
SelectedAttribute in {"SPH"},
VAR Sales =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= StartMonth,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Sales",
ALL( Attributes )
)
VAR Hours =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= StartMonth,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Hours",
ALL( Attributes )
)
VAR Result = DIVIDE( Sales, Hours )
RETURN
Result
)
MEASURE Data[Month0] =
var SelectedAttribute = SELECTEDVALUE( Attributes[Attribute] )
var DateSelected = SELECTEDVALUE( Dates[Date] )
var EndMonth = EOMONTH ( DateSelected, 0 )
return
SWITCH( TRUE(),
SelectedAttribute in {"Hours", "Sales"},
VAR Result =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= DateSelected,
Dates[Date] <= EndMonth
)
return
Result,
SelectedAttribute in {"SPH"},
VAR Sales =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= DateSelected,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Sales",
ALL( Attributes )
)
VAR Hours =
CALCULATE (
SUM ( Data[Value] ),
Dates[Date] >= DateSelected,
Dates[Date] <= EndMonth,
Attributes[Attribute] = "Hours",
ALL( Attributes )
)
VAR Result = DIVIDE( Sales, Hours )
RETURN
Result
)
And here's your Attributes table:
And the final result:
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |