Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm wondering if someone can help me....
I have a large data set, but its market intelligence data, so it only has Quarter as the date column, so firstly I have had to make a Many:Many bi-directional link to the date table.
My problem is when I want to create a table to show the top 5 companies, plus 'Others' for the rest and then add YoY measures.
This is what I've done so far..
1. I created a Summary Table of the fact table:
Summary = SUMMARIZECOLUMNS('Full Data Table'[Company], 'Full Data Table'[Quarter] , 'Full Data Table'[Lenovo_Segment],"Units",sum('Full Data Table'[Units]))
2. I created a new column in the summary table and used RankX to rank by Company
Rank by units = Rankx(CALCULATETABLE(Summary,ALLEXCEPT(Summary,Summary[Lenovo_Segment], Summary[Quarter])),Summary[Units])
3. I added a column with an if Statment to list the top 5 companies and make all remaining compnies 'Others'
Top 5 = IF (Summary[Rank by units] <= 6 && Summary[Company] <> "Others",Summary[Company], "Others")
I have then created a table as below.
Notice 'Units Ths Year' works perfectly
But 'Units last year does not as the ranking has changed and Dell from previous year is added when its ourside of this years ranking.
Becuase i want to include'others' for comapnies outside the top 5 I believe I need to be able to calculate the units for last year baesed on the company rank for this year. I cannot work out how to do this.
Can anyone please help?
@Anonymous , You can create a date from Qtr and year. seprate out Year and qtr and follow my video : https://youtu.be/yPQ9UV37LOU
or create a new qtr year table (date) with new columns for qtr No and Year using left and right functions
Qtr Rank = RANKX(all('Date'),'Date'[Qtr year],,ASC,Dense)
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Np] <=max([Qtr no])))
Thanks for your reply. You have missed my reason fro using the Summary table - this was so I can show the top 5 company and then place all remaining companies under the name 'Others'. This works for current Qtr, but does not work when i want yo do YOY calculations.
It is the method of creating the 'Others' row which is giving me issues when I want the YOY calculation.
Any idea how I could keep 'Others' and have YOY ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |