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 display properly.

 

Sample pbix file - https://gofile.io/?c=0QVsbo

 

The desired views are attached, could anyone assist?  

 

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

3 Replies

  • 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

    • NewYorkNY's avatar
      NewYorkNY
      Regular Visitor
      7 years ago

      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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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