cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
shanelthiggs Frequent Visitor
Frequent Visitor

Matrix Column Headers - Measures before Dimension (SSAS)

Hi All

 

Firstly, apologies if this has already been answered/covered in a previous thread; I've not managed to find anything thus far and did try to (probably wrongly) tag on to a different question in the hope of a solution.

 

As an example, we can have a list of regions/locations, and then by month show the measures volume and revenue. These will appear as what I would call a secondary header, or sub-heading beneath the months, so you end up with volume and revenue per month. What we would like to be able to do, is reverse the header logic in this scenario, so that we end up with each month per measure, i.e. Volume - September, October, November / Revenue - September, October, November.

 

We can do this using our existing SSAS product, as demonstrated in my images below, where we are able to drag the measures into the columns section (can be done for rows also), and position them after OR before the dimension header. The first image is what we can replicate in PBI, and the second image is what we'd like to be able to do.

 

bat op1.JPGDimension Headers Primary / Measures Headers Secondarybat op2.JPGMeasures Headers Primary / Dimensions Headers Secondary

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks for any help in advance.

 

Shane

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Matrix Column Headers - Measures before Dimension (SSAS)

HI @shanelthiggs,

 

I think you need some additional steps to achieve your requirement.

 

Steps:

1. Enter to query editor.

2. Choose 'Volume units' and 'Revenue Net', then use 'unpivot columns' feature to transfer them to attribute and value.
3. Save change and return to report view.

4. Drag attribute to column fields and value to value field.(notice: resort column filed and mark sure month column is in the bottom)

 

BTW, if 'Volume units' and 'Revenue Net' means measures, it is impossible to achieve your requirement. Current power bi can't use measure on row or column fields.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
6 REPLIES 6
Super User
Super User

Re: Matrix Column Headers - Measures before Dimension (SSAS)

Hi @shanelthiggs

 

Try this.

 

1. Create a table with columns as ID and Name.  With values as 1, Volume and 2, Revenue

2. In the modelling tab , set the sort order for the Column Name as ID.

3. Use the Name as Row , followed by Month

4. In the values put the measures - Sales and Revenue.

 

If this works please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
shanelthiggs Frequent Visitor
Frequent Visitor

Re: Matrix Column Headers - Measures before Dimension (SSAS)

Hi @CheenuSing - thanks for the reply.

 

I'm not sure that's going to work for us, as we're linking directly to a SSAS Cube for the data to form the matrix. The below might explain a little more what I'm hoping to achieve; effectively, get the values as columns.

 

In the third image, on the left hand side (1) is the output we're given, and the right hand side (2) is the output we'd like to be able to achieve.


Thanks

Shane

 

pbi_matrix_current.JPGCurrentpbi_matrix_desired.JPGDesiredpbi_matrix_output.JPGDesired Output

 

 

Community Support Team
Community Support Team

Re: Matrix Column Headers - Measures before Dimension (SSAS)

HI @shanelthiggs,

 

I think you need some additional steps to achieve your requirement.

 

Steps:

1. Enter to query editor.

2. Choose 'Volume units' and 'Revenue Net', then use 'unpivot columns' feature to transfer them to attribute and value.
3. Save change and return to report view.

4. Drag attribute to column fields and value to value field.(notice: resort column filed and mark sure month column is in the bottom)

 

BTW, if 'Volume units' and 'Revenue Net' means measures, it is impossible to achieve your requirement. Current power bi can't use measure on row or column fields.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
shanelthiggs Frequent Visitor
Frequent Visitor

Re: Matrix Column Headers - Measures before Dimension (SSAS)

Thank you @v-shex-msft - they are measures so as you said, will not be possible to achieve this in current versions.

 

Accepted as solution.

CerbPBI Frequent Visitor
Frequent Visitor

Re: Matrix Column Headers - Measures before Dimension (SSAS)

How did you achieve what's in your 1st screenshot.

I'm getting such arrangement with measure values as row lables. Is there a matrix setting that I need to apply to achieve the screenshot#1 look?

Are you Units and Revenue measures? Appreciate your feedback.

CerbPBI Frequent Visitor
Frequent Visitor

Re: Matrix Column Headers - Measures before Dimension (SSAS)

Never mind. If I turn off "Show on Rows" in Values section, I get the desired output.