Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
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
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
Solved! Go to Solution.
@Anonymous
Please see if the following logic works for you for my table.
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.
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
)
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.
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
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
)
@Anonymous
Please see if the following logic works for you for my table.
Hi
Thanks for the help
I've tried, but unfortunatelly it doesn't work
Anyway thank you
Diego
@Anonymous provide sample and expected output for a starter
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
44 | |
35 | |
25 | |
22 | |
22 |