Forum Discussion

shanelthiggs's avatar
shanelthiggs
Regular Visitor
8 years ago
Solved

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 questi...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • shanelthiggs's avatar
    shanelthiggs
    Regular Visitor
    8 years ago

    Hi Anonymous - 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

     

    CurrentDesiredDesired Output

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • shanelthiggs's avatar
        shanelthiggs
        Regular Visitor
        8 years ago

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

         

        Accepted as solution.

    • CerbPBI's avatar
      CerbPBI
      Frequent Visitor
      7 years ago

      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's avatar
        CerbPBI
        Frequent Visitor
        7 years ago

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