Forum Discussion

TJ's avatar
TJ
New Member
8 years ago
Solved

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

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    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,

3 Replies

  • TJ's avatar
    TJ
    New Member
    8 years ago

    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

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      8 years ago

      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,

      • TJ's avatar
        TJ
        New Member
        8 years ago

        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