Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Ranking dataset based in different levels of granularity

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:

BrandCat1Cat2Cat3Sales QuantitySales MS %Rank by MS %
ABCBeautyBodyBody Milk1000 50%1
XYZBeautyBodyBody Milk800 40%2
DZABeautyBodyBody Milk200 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:

BrandCat1Cat2Sales QuantitySales MS %Rank by MS %
ABCBeautyBody1000 50%1
ABCBeautyHair300 30%2
XYZBeautyBody800 40%2
XYZBeautyHair500 50%1
DZABeautyBody200 10%3
DZABeautyHair200 20%3
DZABeautyHands250 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:

BrandCat1Sales QuantitySales MS %Rank by MS %
ABCBeauty1000 50%1
ABCHome300 30%2
ABCMen's Clothing300 30%2
XYZBeauty800 40%2
XYZHome500 50%1
XYZMen's Clothing500 50%1
DZABeauty200 10%3
DZAHome200 20%3
DZAMen's Clothing200 20%3
DZAWomen's Clothing200 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

 

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@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
)

shwetadalal_0-1641819060043.png

 

 

 

View solution in original post

v-janeyg-msft
Community Support
Community Support

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

vjaneygmsft_0-1641901451218.pngvjaneygmsft_1-1641901454716.pngvjaneygmsft_2-1641901460119.png

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

 

View solution in original post

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
    )

vjaneygmsft_1-1642646913530.png

Best Regards,
Community Support Team _ Janey

View solution in original post

7 REPLIES 7
v-janeyg-msft
Community Support
Community Support

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

vjaneygmsft_0-1641901451218.pngvjaneygmsft_1-1641901454716.pngvjaneygmsft_2-1641901460119.png

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

 

Anonymous
Not applicable

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

carlovsky_0-1642356726696.png

 

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
    )

vjaneygmsft_1-1642646913530.png

Best Regards,
Community Support Team _ Janey
Anonymous
Not applicable

@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
)

shwetadalal_0-1641819060043.png

 

 

 

Anonymous
Not applicable

Hi

 

Thanks for the help

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

 

Anyway thank you

 

Diego

Anonymous
Not applicable

Hi @smpa01 

 

Thank you very much for the tips

I've just updated my initial message

 

Thanks a lot

Diego

smpa01
Super User
Super User

@Anonymous  provide sample and expected output for a starter

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.