topic Re: Ranking dataset based in different levels of granularity in DAX Commands and Tips

Ranking dataset based in different levels of granularity

Anonymous — Sun, 09 Jan 2022 13:37:46 GMT

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

Re: Ranking dataset based in different levels of granularity

smpa01 — Thu, 06 Jan 2022 21:19:39 GMT

Anonymous provide sample and expected output for a starter

Re: Ranking dataset based in different levels of granularity

Anonymous — Sun, 09 Jan 2022 13:38:43 GMT

Hi smpa01

Thank you very much for the tips

I've just updated my initial message

Thanks a lot

Diego

Re: Ranking dataset based in different levels of granularity

Anonymous — Mon, 10 Jan 2022 12:51:08 GMT

Anonymous

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

)

Re: Ranking dataset based in different levels of granularity

Anonymous — Mon, 10 Jan 2022 12:27:03 GMT

Thanks for the help

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

Anyway thank you

Diego

Re: Ranking dataset based in different levels of granularity

v-janeyg-msft — Tue, 11 Jan 2022 11:46:49 GMT

Hi, Anonymous

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

Re: Ranking dataset based in different levels of granularity

Anonymous — Sun, 16 Jan 2022 18:12:48 GMT

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

Re: Ranking dataset based in different levels of granularity

v-janeyg-msft — Thu, 20 Jan 2022 02:50:42 GMT

Hi, Anonymous

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