Column Variance

[schlaj]

Hello,

I have a column across the top of my crosstab that I control the number of columns that show up by a slicer. Ex: state column where the crosstab columns are each state. The measure for the table populates the center of the crosstab, and the rows are various segments of our industry. I want to be able to compare state by state % variances by segment. All fields are in the same table. How would I calculate this? This is an example of what I'm looking for:

[v-yulgu-msft · Accepted solution]

Hi @schlaj,

I tested with below sample table. Add an index column first, and set its data type to "Whole number".

index Col = RIGHT(Sheet1[State],1)

Add below measure and corresponding fields into a Matrix visual.

variance =
VAR previoussteat =
    CALCULATE (
        SUM ( Sheet1[Value] ),
        FILTER (
            ALLEXCEPT ( Sheet1, Sheet1[Segment] ),
            Sheet1[index Col]
                = MAX ( Sheet1[index Col] ) - 1
        )
    )
RETURN
    IF ( previoussteat <> BLANK (), SUM ( Sheet1[Value] ) - previoussteat )
        / previoussteat

Best regards,

Yuliana Gu

[schlaj]

Thanks @v-yulgu-msft,

Is there any way that I can make this responsive to a slicer? So if I take out state 2, I can compare State 1 to State 3?