Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
17 |
User | Count |
---|---|
33 | |
25 | |
18 | |
15 | |
13 |