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

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