Hello,
I need some help solving the below requirement.
I have Fact table like below.
Quater | ProductCategory | ProductSubCategory | Platform | GameType | Region | PublisherName | Genre | Title | PeformancePeriod | Revenue |
2017,Q1 | ABC | DEF | One | First | NA | AA | Act | SPIRIT | First Month | 1230 |
2017,Q1 | ABC | DEF | Two | First | CA | AA | Act | SPIRIT | First Month | 3230 |
2017,Q2 | ABC | GHI | One | First | TX | BB | Act | SPIRIT | First Month | 2301 |
2017,Q1 | XYZ | JKL | One | First | NA | BB | Adv | SW | First Month | 4230 |
2017,Q1 | XYZ | MNO | One | First | CA | BB | Adv | SW | First Month | 5423 |
2017,Q2 | XYZ | JKL | Two | First | TX | AA | Adv | SW | First Month | 2523 |
2017,Q2 | XYZ | MNO | One | First | NA | AA | Adv | SPIRIT | First Month | 3323 |
I want My Final Output to be like below.(enteries in this out put are not exactly related to above Fact table).
Percentage (based on Titles) | Revenue |
Top 20% | 24,906,686 |
21-40% | 3,332,981 |
41-60% | 526,956 |
61-100% | 70,362 |
To achive above output, below is the logic i tried which didn't work.
Firstly, I tried giving rank by grouping all the other fields other than Title, using below DAX Formula ( I want to get Rank based on Distinct titles that means,, duplicate titles will share same Rank).
Rank = RANKX (ALL'Rank'[Genre],'Rank'[GameType],'Rank'[Platform],'Rank'[Quarter],'Rank'[PublisherName],'Rank'[PerformancePeriod],'Rank'[ProductCategory],'Rank'[ProductSubCategory]),
CALCULATE ( DISTINCTCOUNT( 'Rank'[Title] ) ),,DESC
)
When i am using above DAX, Rank column is being shown as 1 for every row. How can it be fixed?
Also, I am using below logic to calculate: Number of tiles based on percentage range.
For example : If I have 92 Titles In my fact, my calculation will be like below
For Top 20%: Round((20*92)/100,0) = 18
For next 21-40% : Round((40*92)/100,0) = 37 -(Top 20%) i.e ( 37-18 )= 19
and so on for other percentage ranges.
Perentage Top 20% 21-40% 41-60% 61-100%
NumTitles 18 19 18 37
Finally, I want my output to be sum of revenue generated by num of titles (For example: for top 20% the titles are 18 so I want to show the revenue generated by top 18 titles)
Percentage | Revenue |
Top 20% | 24,906,686 |
21-40% | 3,332,981 |
41-60% | 526,956 |
61-100% | 70,362 |
