How do i add Ranking to Multiple Rows with the same brand but different models

[Anonymous]

Hi, 

I am new to Power BI and need your assistance

Basically i have a table in power BI as below which i would like to rank by item based on number of units sold, The only thing is there are multiple rows within the table for each brand with a unique model, when i try to rank this it does not provide me with the correct Ranking as well as Top 20 ranking for any selected period.

Brand                     Item                  Sales units

Brand A                   xx                            25

Brand A                   ff                             28

Brand B                   rr                             45

Brand C                   tt                             02

Brand C                   jj                              85

I would like to Rank the above but based on the highest units sold and then at any point it should also rank by Brand if i select a specific brand.

Your assistance is greatly appreciated.

[v-juanli-msft]

Hi @Anonymous 

"I would like to Rank the above but based on the highest units sold and then at any point it should also rank by Brand if i select a specific brand."

From above, it seems you want to rank sales units by brand on descending order.

To get this, Create measures

Measure = SUM(Sheet2[Sales units])

Measure 2 = RANKX(ALLSELECTED(Sheet2),[Measure],,DESC)

To show Top n rank, create a Top N table without being connected with your data table, then create a measure and add this meausre in the visual level filter of the table visual.

Measure 3 = IF([Measure 2]<=SELECTEDVALUE('Top N'[Top N]),1,0)

Download my pbix and see more details.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TCarrasquillo]

Hi,

It would be helpful if you posted the formula/measure you were using for ranking, but I think the first thing to try would be to use "ALLSELECTED" instead of "ALL" if you are not already.

I hope that works but if not, try showing your formula. 🙂

[Anonymous]

Hi, thank you for responding. 

This is my initial formula

Rank Units Sold =

RankX (All('Table'),

CALCULATE(SUM('Table'[Sales Units])),,DESC,Dense)

Now i tried adding allselected but its still giving me Ranking For each brand and not outlining the Individual ranking for each item.

I trust this makes sense, as in addition i would like this to dynamically change when i select a different Business Unit(Segment)

Thanks  

[v-juanli-msft]

Hi @Anonymous 

As tested, with this formula, 

Measure 2 = RANKX(ALLSELECTED(Sheet2),CALCULATE(SUM(Sheet2[Sales units])),,DESC)

It is ranked for each item, when i select a brand, it will rank for each item of the brand.

How about writing another meausre

Measure = SUM(Sheet2[Sales units])

Then use this measure in the rank formula

Measure 2 = RANKX(ALLSELECTED(Sheet2),[Measure],,DESC)

If it doesn't help, please show an screenshot of yours,

or download my pbix from my previous reply, based on my pbix, tell me what's wrong i make to understand your requirement, give the correct rank result you expected.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.