Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Firstly, you need to create a rank calculated column like below:
Title Rank = RANKX ( ALL ( Table2[Title ] ), CALCULATE ( SUM ( Table2[Revenue] ), ALLEXCEPT ( Table2, Table2[Title ] ) ), , DESC, DENSE )
Then you can add a "percentage" column like:
Pct = Table2[Title Rank] / CALCULATE ( DISTINCTCOUNT ( Table2[Title] ), ALL ( Table2 ) )
Use SWITCH() to assign each "bucket":
Bucket = SWITCH ( TRUE (), Table[Pct] > 0 && Table[Pct] <= 0.2, "Top20%", Table[Pct] > 0.2 && Table[Pct] <= 0.4, "21%-40%", Table[Pct] > 0.4 && Table[Pct] <= 0.6, "41%-60%", Table[Pct] > 0, 6 && Table[Pct] <= 1, "61%-100%" )
Now you just put above Bucket column and Revenue column into Table visual to get your expected result.
Regards,
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 |
Firstly, you need to create a rank calculated column like below:
Title Rank = RANKX ( ALL ( Table2[Title ] ), CALCULATE ( SUM ( Table2[Revenue] ), ALLEXCEPT ( Table2, Table2[Title ] ) ), , DESC, DENSE )
Then you can add a "percentage" column like:
Pct = Table2[Title Rank] / CALCULATE ( DISTINCTCOUNT ( Table2[Title] ), ALL ( Table2 ) )
Use SWITCH() to assign each "bucket":
Bucket = SWITCH ( TRUE (), Table[Pct] > 0 && Table[Pct] <= 0.2, "Top20%", Table[Pct] > 0.2 && Table[Pct] <= 0.4, "21%-40%", Table[Pct] > 0.4 && Table[Pct] <= 0.6, "41%-60%", Table[Pct] > 0, 6 && Table[Pct] <= 1, "61%-100%" )
Now you just put above Bucket column and Revenue column into Table visual to get your expected result.
Regards,
Hi sihou,
Sorry for the delay in my response. Thank you so much for responding to my post.
I have gone through you reply and my requirement is bit different. Sorry, if i was not clear in my initial post. But, below is what I need.
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
I need to use the exact formula I mentioned above as per the business requirement. So, once i get number of titles for a percentage range, i want sum of the revenue for those titles to be shown for that particular percentage range.
To be more clear,
top 20% should show sum of revenue of 1st-18th titles (since, we got 18 titles)
top 21-40% should show sum of revenue from19th-38th titles (Since we got 19 titles as per the formula)
I'm guessing, we would need to use 'offset' which is available in power achive this. But, i'm not familar on using it.
Could you please check on this one more time and let me know the way this can be achived. Thanks for your time.
Thanks,
TJ
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |