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
TJ
New Member

Need Help

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

1 ACCEPTED SOLUTION

@TJ

 

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,

View solution in original post

3 REPLIES 3
TJ
New Member

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

@TJ

 

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

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.