Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
Solved! Go to 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
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
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 Regards!
Dale
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |