Forum Discussion

NewYorkNY's avatar
NewYorkNY
Regular Visitor
7 years ago
Solved

Crosstabbing Dataset and Subtotaling Chg% Formulas

Hi there, new to Power BI with a strong excel background. I have a dataset that I'm looking to build some crosstabs and growth formulas in but i'm having trouble using the matrix to get the data to d...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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