Best practices for pivoting dimension

[s15]

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.

[v-jiascu-msft · Accepted solution]

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]

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]

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

[s15]

Hi @v-jiascu-msft

That's correct. I want to extract two value of year (2013 and 2014) from the Years to add to slicer chart. That's one of the reason why the question came out.

Thank you.