Putting Ranking Measure as Axis

[Anonymous]

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

Here is the link to the solution which I had attemped:

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!!

[AlB · Accepted solution]

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]

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!!