Forum Discussion

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 ...

Need Help

v-sihou-msft

8 years ago

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,

v-sihou-msft

Microsoft Employee

8 years ago

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,

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,