Forum Discussion
Crosstabbing Dataset and Subtotaling Chg% Formulas
Hi NewYorkNY ,
You can refer to following steps to achieve your requirement.
Steps:1. Enter to query editor and do 'unpivot columns' on 'sales' and 'revenue' columns.
2. Write a measure formula with if statement to change total level calculation.Measure = IF ( ISFILTERED ( 'Table'[Type] ), SUM ( 'Table'[Value] ), VAR _org = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Org" ) VAR _var = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Var" ) RETURN DIVIDE ( _var - _org, _org ) )3. Create matrix visual with name, date as rows, attribute, type as column, measure as value.
Regards,
Xiaoxin Sheng
Hi NewYorkNY ,
You can refer to following steps to achieve your requirement.
Steps:
1. Enter to query editor and do 'unpivot columns' on 'sales' and 'revenue' columns.
2. Write a measure formula with if statement to change total level calculation.
Measure =
IF (
ISFILTERED ( 'Table'[Type] ),
SUM ( 'Table'[Value] ),
VAR _org =
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Org" )
VAR _var =
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Var" )
RETURN
DIVIDE ( _var - _org, _org )
)
3. Create matrix visual with name, date as rows, attribute, type as column, measure as value.
Regards,
Xiaoxin Sheng
Thank you! This works, is there a way I can assign the metric type for each the value? So Revenue in $, Sales in whole numbers, and Chg in percent?
Hi NewYorkNY ,
Nope, you can't accurate control matrix fields data format.
Maybe you can do format in measure formula to force change total level to percent style, but it will also change measure to text type.
Regards,
Xiaoxin Sheng
