Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Measure as rows and columns in Matrix visual

Hello,

 

I am trying to make a matrix visiual with a measure as the rows and columns. I would like to show how much product sales for products are up or down compared to past and future dates, see example output below.

 

The values are the number of products in the group. For example, the 21 I have highlighted shows that 21 products only had increased sales between 3-6 compared to same period last year but future orders are up11+ compared to same period last year. The majority of products are +- 2 sales Sales Matrix

 

 

 

 

 
 

 

In my data model I have  2 tables for sales linked to 2 calender tables (this year and last year) and a products table linked to both sales tables.

 

I can create measures to get the values but I can't use the group measure in a matrix visual as both rows and columns?

 

I have considered making a new table with these measures as calculated colums but i'm not sure of the DAX needed for that and if it will filter by date slicers used in the report?

 

Thanks in advance!

Need Help

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    As you said, we can’t  use measures as axis or legend in visuals.
    You can create two calculated columns which is used to divide and mark the range of sales. Like this:

     

    “if it will filter by date slicers used in the report?”
    From my guess, filters can be applied to it.

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.




    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Lionel,

       

      Thnaks for your response. I read many times that calculated colums aren't usually the way to go but I don't see another option if the visual will only use a column in the column field and not a measure.

       

      I am now stuck on creating the columns because i don't know how to do calculations across multiple tables and ensuring the filte rthe same way as my measures?

       

      The calculated columns i need would be "sales this year", "sales last year" to get "sales difference". The the sales difference would then be used to create the colum conating the groups e.g Down3-6 or UP3-6 etc.

       

      Would I need to created a seperate table for these calcualated columns and make relationships with my model or could i just start adding the columns to my products table?

       

      Frusting that i'm able easily achieve the calcualtions with measures but not display them in a matrix.

       

      Thanks for your help

       

      David