Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |