Forum Discussion

s15's avatar
s15
Icon for Helper III rankHelper III
9 years ago
Solved

Best practices for pivoting dimension

Hi all,

 

I have a dataset named financial data which comprises of list of products, sales, segment and country or so on (see the screen below)

 

 

Now I need to see which product, segment and country by year 2013 and 2014 to compare. What I did was to duplicate the financial table then remove uneccessary columns. That said, there are three duplicated tables

  • sales_product_by_year: consists of product, sales and year column
  • sales_segment_by_year: consists of segment, sales and year column
  • sales_country_by_year: consists of country, sales and year column

 

I then pivoted each of the three above tables

 

 

 

I think I have completed the work and these charts meet my expectation. However I'm not sure if this is a practice to do for pivoting. Everytime if you need to pivot a dimension, do you have to create a specific table. Is there a way to combine all of three tables in my case into a table and use Dax to query?

Your suggestion is always appreciated.

 

 

Need Help
Tips and Tricks
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi s15,

     

    Yes, we need one table and two measures in this scenario. I believe there are also other ways.

    I sampled a table from yours. And the two measures are:

     

    Sales2013 =
CALCULATE ( SUM ( 'Financials'[Sales] ), 'Financials'[Year] = 2013 )
    Sales2014 =
CALCULATE ( SUM ( Financials[Sales] ), 'Financials'[Year] = 2014 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi s15,

     

    Yes, we need one table and two measures in this scenario. I believe there are also other ways.

    I sampled a table from yours. And the two measures are:

     

    Sales2013 =
CALCULATE ( SUM ( 'Financials'[Sales] ), 'Financials'[Year] = 2013 )
    Sales2014 =
CALCULATE ( SUM ( Financials[Sales] ), 'Financials'[Year] = 2014 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • s15's avatar
      s15
      Icon for Helper III rankHelper III
      9 years ago

      Thank you very much for your support v-jiascu-msft 

       

      Measure is really useful and it reduces my effort to duplicate my table. However, if I want to add 2013 and 2014 to my slicer chart, measure does not help right? In this case I would still need to duplicate my table and remove unnecessary column.

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi s15,

         

        Measure can't be added in the slicer visual by now. Do you mean it as the picture shows? Or you could post it here. Maybe we could find out a workaround.

        BTW, I am going to update the two measures, which will be more accurate.

         

        Sales2013 =
CALCULATE (
    SUM ( 'Financials'[Sales] ),
    FILTER ( 'Financials', 'Financials'[Year] = 2013 )
)
        Sales2014 =
CALCULATE (
    SUM ( Financials[Sales] ),
    FILTER ( 'Financials', 'Financials'[Year] = 2014 )
)

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale