Add a measure to unpivoted data

[powerbiuser444]

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!

[powerbiuser444]

@daxer-almighty Appreciate your time and help!

[daxer-almighty · Accepted solution]

@powerbiuser444 

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: