Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
s15
Helper III
Helper III

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)

 

finance.PNG

 

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

table.PNG

 

I then pivoted each of the three above tables

 

 chart.PNG

 

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.

 

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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 practices for pivoting dimension.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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 practices for pivoting dimension.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

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.

 

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 practices for pivoting dimension2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.