Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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
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
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?