cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Putting Ranking Measure as Axis

So I read a thing about Cumulative Sum solutions some time ago, and it did work like a charm.

https://www.pbiusergroup.com/blogs/victor-velarde/2017/07/07/como-realizar-un-diagrama-8020-o-de-dis...

What I did was changed the Nombre to Brands, and Cantidad to Sales Units.

So that I will get Cumulative Sales Units by Brands. (Sorted by Sales Units, from highest to lowest)

But what I need to show is Cumulative Sales Units by number of Brands.

In Table Visual:

What I have: (when Region = X, there are several other regions)             What I need:
Brands              Sales Units                Cumul SU              Region           Number of Brands

J                             50                              50                          X                           1
A                            28                              78                          X                           2
M                           15                              93                          X                           3
E                             5                                98                         X                           4
S                             2                               100                        X                           5

What I am trying to show is how are the brands coverage over the market, e.g. 1 brand covers 50% of the market (50/100) in region X, on a Line Chart Visual. I can change the Cumul SU to Percentage of Grand Total, but I don't know how to create the x axis.

Initially I though to make a dynamic measure of ranking for Sales Units, but I ran out of idea of how to.

Edit: I have come up with the solution to show the dynamic ranking, in this case, similar to Number of Brands. But it only works in Table Visual.

Ranking Measure, this gives me the "What I need" above.

```Ranking :=    RANKX(      ALLSELECTED('Data'[Brands]),       RANKX(         ALLSELECTED('Data'[Brands]),         CALCULATE(SUM('Data'[Sales Units]))      ), , ASC
)```

Unfortunately, I can't visual these numbers as X axis in Line Chart Visual, I could put in on the tooltip, but what I want is to put it as X axis.

Any suggestions? Thank youu!!

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous

I'm not sure whether you want a calculated column in your table or a table visual. I've assumed the latter for now. Try this, where Table1 is the table you show without the 'Number of Brands' column

1. Set Brands, Sales Units, Cumul SU and  Region region in a table visual. Make sure all of them are set to 'Don't summarize'.

2. Create this measure and place it in the visual

```Measure =
RANKX (
CALCULATETABLE ( Table1; ALLEXCEPT ( Table1; Table1[Region] ) );
Table1[Sales Units];
SELECTEDVALUE ( Table1[Sales Units] );
DESC
)```
2 REPLIES 2
Super User

Hi @Anonymous

I'm not sure whether you want a calculated column in your table or a table visual. I've assumed the latter for now. Try this, where Table1 is the table you show without the 'Number of Brands' column

1. Set Brands, Sales Units, Cumul SU and  Region region in a table visual. Make sure all of them are set to 'Don't summarize'.

2. Create this measure and place it in the visual

```Measure =
RANKX (
CALCULATETABLE ( Table1; ALLEXCEPT ( Table1; Table1[Region] ) );
Table1[Sales Units];
SELECTEDVALUE ( Table1[Sales Units] );
DESC
)```
Anonymous
Not applicable

Hi @AlB , thanks for the reply.

Sorry, I should had mentioned it in the post. I planned to show it with the Line Chart Visual.

So the X axis will be: number of Brands, and

Y axis will be: Cumul SU (%)

But I will try to work from your solution first, maybe I will get a better presentation. Thanks a lot!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.