Ranking dataset based in different levels of granularity

[carlovsky]

Hello everyone,

I hope everyone that's reading this message is having an wonderful day

I'm running into a problem, that I would really appreciate your help 🙂

I have a dataset in PBI, where I have to Rank per 3 different LEVELS of granularity(Cat1,Cat2,Cat3):

WHAT I WANT?

Sales Rank per Category 1 and Brand: 

I want to rank by the total Sales(Brand in Category 1) / Total Sales(Cat1) 

Sales Rank per Category 2 and Brand: 

I want to rank by the total Sales(Brand in Category 2) / Total Sales(Cat2) 

Sales Rank per Category 3 and Brand: 

I want to rank by the total Sales(Brand in Category 3) / Total Sales(Cat3) 

Explaining further:

Example 1:

Having Category 1 filtered, Category 2 filtered and Category 3 filtered I want to:

Rank by the Sales of each Brand in each Category 3 Item / Total Sales(Cat3 item)

Columns Cat1, Cat2 and Cat3 are filtered in the Matrix

Company ABC has 1000$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Body and segment from Cat3 Body Milk

Company XYZ  has  800$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Body and segment from Cat3 Body Milk

Company DZA has  200$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Body and segment from Cat3 Body Milk

Find in red the expected OUTPUT

Expected Output:

Brand	Cat1	Cat2	Cat3	Sales Quantity	Sales MS %	Rank by MS %
ABC	Beauty	Body	Body Milk	1000	50%	1
XYZ	Beauty	Body	Body Milk	800	40%	2
DZA	Beauty	Body	Body Milk	200	10%	3

Example 2:

Having Category 1 filtered, Category 2 filtered and Category 3 NOT filtered I want to:

Rank by the Sales of each Brand for each Category 2 Item / Total Sales(Cat2 item)

Example:

Columns Cat1, Cat2 are filtered in the Matrix

Company ABC has 1000$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Body 

Company ABC has 300$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Hair 

Company XYZ  has  800$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Body

Company XYZ  has  500$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Hair

Company DZA has  200$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Body

Company DZA has  200$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Hair

Company DZA has  250$ in Sales in the segment from Cat1 Beauty and segment  from Cat2 Hands

Find in red the expected OUTPUT

Expected Output:

Brand	Cat1	Cat2	Sales Quantity	Sales MS %	Rank by MS %
ABC	Beauty	Body	1000	50%	1
ABC	Beauty	Hair	300	30%	2
XYZ	Beauty	Body	800	40%	2
XYZ	Beauty	Hair	500	50%	1
DZA	Beauty	Body	200	10%	3
DZA	Beauty	Hair	200	20%	3
DZA	Beauty	Hands	250	100%	1

Example 3:

Having Category 1 filtered, Category 2 NOT FILTERED and Category 3 NOT FILTERED I want to:

Rank by the Sales of each Brand for each Category 1 Item / Total Sales(Cat1 item)

Example:

Columns Cat1, Cat2 are filtered in the Matrix

Company ABC has 1000$ in Sales in the segment from Cat1 Beauty 

Company ABC has 300$ in Sales in the segment from Cat1 Home

Company ABC has 300$ in Sales in the segment from Cat1 Men's Clothing

Company XYZ  has  800$ in Sales in the segment from Cat1 Beauty 

Company XYZ  has  500$ in Sales in the segment from Cat1 Home

Company XYZ  has  500$ in Sales in the segment from Cat1 Men's Clothing

Company DZA has  200$ in Sales in the segment from Cat1 Beauty 

Company DZA has  200$ in Sales in the segment from Cat1 Home 

Company DZA has  200$ in Sales in the segment from Cat1 Men's Clothing

Company DZA has  200$ in Sales in the segment from Cat1 Women's Clothing

Find in red the expected OUTPUT

Expected Output:

Brand	Cat1	Sales Quantity	Sales MS %	Rank by MS %
ABC	Beauty	1000	50%	1
ABC	Home	300	30%	2
ABC	Men's Clothing	300	30%	2
XYZ	Beauty	800	40%	2
XYZ	Home	500	50%	1
XYZ	Men's Clothing	500	50%	1
DZA	Beauty	200	10%	3
DZA	Home	200	20%	3
DZA	Men's Clothing	200	20%	3
DZA	Women's Clothing	200	100%	1

DUMMY DATA:

Please find in the link

PBI File and Dataset 

What I need from you?

1) The Measure Market Share Volume % is well built? I Want that the measure calculates based in the Cat filtered

MS Volume % =

DIVIDE (

CALCULATE ( SUM(FACT_SALES[SALES_QTY] )),

CALCULATE (

SUM (FACT_SALES[SALES_QTY] ),

ALLEXCEPT (DIM_PRODUCT,DIM_PRODUCT[Cat1],DIM_PRODUCT[Cat2],DIM_PRODUCT[Cat3] )

),

)

2) What could be an example formula to give me what I want? I've tried with RANKX several times, but not seeing the

expected results (you can find Rank MS % Measure that i'm trying to build in the link sent)

Thank you very much

Diego

[Anonymous · Accepted solution]

@carlovsky 

Please see if the following logic works for you for my table.

_ranking =

VAR _maxArea =

MAX ( 'Table'[Area] )

RETURN

RANKX (

FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Area] = _maxArea ),

SUM('Table'[Net Revenues]),

,

DESC

)

[v-janeyg-msft · Accepted solution]

Hi, @carlovsky 

I created some measures using your sample file.

Like this:

Rank by MS% = 
RANKX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],[Cat1],[Cat2],[Cat3],"a",SUM(FACT_SALES[SALES_QTY])),[Sales MS%])

Rank by MS% 2 = 
RANKX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],[Cat1],"a",SUM(FACT_SALES[SALES_QTY])),[Sales MS% 2])

Rank by MS% 3 = 
RANKX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],"a",SUM(FACT_SALES[SALES_QTY])),[Sales MS% 3])

Sales MS% = 
var a=SUMX(SUMMARIZE(FACT_SALES,[Brand],[Cat1],[Cat2],[Cat3],"a",SUM(FACT_SALES[SALES_QTY])),[a])
var b=SUMX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],[Cat1],[Cat2],[Cat3],"a",SUM(FACT_SALES[SALES_QTY])),[a])
return a/b

Below is my sample. Hope it helps.

I will take a long vacation and can't reply in time. Please check the details carefully and modify it according to your needs.

Did I answer your question? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft · Accepted solution]

Hi, @carlovsky 

I created some measures using your sample file.

Like this:

Rank by MS% = 
RANKX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],[Cat1],[Cat2],[Cat3],"a",SUM(FACT_SALES[SALES_QTY])),[Sales MS%])

Rank by MS% 2 = 
RANKX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],[Cat1],"a",SUM(FACT_SALES[SALES_QTY])),[Sales MS% 2])

Rank by MS% 3 = 
RANKX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],"a",SUM(FACT_SALES[SALES_QTY])),[Sales MS% 3])

Sales MS% = 
var a=SUMX(SUMMARIZE(FACT_SALES,[Brand],[Cat1],[Cat2],[Cat3],"a",SUM(FACT_SALES[SALES_QTY])),[a])
var b=SUMX(SUMMARIZE(ALLSELECTED(FACT_SALES),[Brand],[Cat1],[Cat2],[Cat3],"a",SUM(FACT_SALES[SALES_QTY])),[a])
return a/b

Below is my sample. Hope it helps.

I will take a long vacation and can't reply in time. Please check the details carefully and modify it according to your needs.

Did I answer your question? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[carlovsky]

Hi @v-janeyg-msft 
Thanks a lot for the answer, it was really good.

Before marking as solution, could you or someone tell me how to avoid PBI retrieving 100% whenever i selected just one brand? I want it to be a % of the total of each category i'm selecting

[v-janeyg-msft · Accepted solution]

Hi, @carlovsky 

You need to modify your measure according to your needs.

Like this:

Measure =
VAR a =
    SUMX (
        SUMMARIZE ( FACT_SALES, [Brand], [Cat1], "a", SUM ( FACT_SALES[SALES_QTY] ) ),
        [a]
    )
VAR b =
    SUMX (
        SUMMARIZE (
            ALLSELECTED ( FACT_SALES ),
            [Brand],
            [Cat1],
            "a", SUM ( FACT_SALES[SALES_QTY] )
        ),
        [a]
    )
VAR c =
    SUMX (
        SUMMARIZE (
            ALL ( FACT_SALES ),
            [Brand],
            [Cat1],
            "a", SUM ( FACT_SALES[SALES_QTY] )
        ),
        [a]
    )
RETURN
    IF (
        COUNTROWS (
            SUMMARIZE (
                ALLSELECTED ( FACT_SALES ),
                [Brand],
                [Cat1],
                "a", SUM ( FACT_SALES[SALES_QTY] )
            )
        ) = 1,
        a / c,
        a / b
    )

Best Regards,

Community Support Team _ Janey

[Anonymous · Accepted solution]

@carlovsky 

Please see if the following logic works for you for my table.

_ranking =

VAR _maxArea =

MAX ( 'Table'[Area] )

RETURN

RANKX (

FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Area] = _maxArea ),

SUM('Table'[Net Revenues]),

,

DESC

)

[carlovsky]

Hi

Thanks for the help

I've tried, but unfortunatelly it doesn't work

Anyway thank you

Diego

[carlovsky]

Hi @smpa01 

Thank you very much for the tips

I've just updated my initial message

Thanks a lot

Diego

[smpa01]

@carlovsky  provide sample and expected output for a starter