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, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors