Crosstabbing Dataset and Subtotaling Chg% Formulas

[NewYorkNY]

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?  

[v-shex-msft · Accepted solution]

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]

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? 

[v-shex-msft]

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