Dynamic TOP (companies having 75% of market at selected period)

[Riff]

Hello!

Lets imagine that we have a dataset like this:

Company	Year	Num Of Sales
a	2017	50
b	2017	60
c	2017	70
d	2017	80
e	2017	90
f	2017	100
a	2018	45
b	2018	55
c	2018	65
d	2018	30
e	2018	20
f	2018	10
a	2019	100
b	2019	20
c	2019	80
d	2019	45
e	2019	30
f	2019	15

And we have to create a matrix with a set of columns representing only Companies having a 75% of market at a selected period (for example, for 2017 it must be only c, d, e and f columns in the matrix)

On changing the period the column set must be automatically changed.

Is this possible?

Thank you!

[v-lid-msft]

Hi @Riff ,

We can use a measure as value to meet your requirement:

Measure = 
VAR sumr =
    COUNTROWS ( ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( SUM ( [Num Of Sales] ) ),
            SUM ( 'Table'[Num Of Sales] ),
            ASC,
            DENSE
        )
            > ROUNDUP ( 0.25 * sumr, 0 ),
        SUM ( 'Table'[Num Of Sales] ), -- Or Other Value You Want To Show
        BLANK ()
    )

BTW, pbix as attached.

Best regards,

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

[Riff]

Hello, @v-lid-msft ! Thank you for you answer.

If i got it right, your solve shows the 75% of total count of companies. I mean, if we have 8 companies, your  solve will always show top 6 of them. Probably I did not explain the task enough clear. I need a list of companies that have 75% of sales at market. So for example if we have 6 companies where one of them has 75 sales at year and every of other 5 companies has only 5 sales (total for all of 6 is 100), then we have to get only 1st company because it has 75% of market alone.

Thank you!

[v-lid-msft · Accepted solution]

Hi @Riff ,

We can use the following measure to archive your requirement:

Measure = 
VAR totalsales =
    SUMX ( ALLSELECTED ( 'Table' ), [Num Of Sales] )
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Index", RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( SUM ( 'Table'[Num Of Sales] ) ),
            ,
            ASC,
            DENSE
        )
    )
VAR t2 =
    ADDCOLUMNS (
        t,
        "total", SUMX (
            FILTER (
                SELECTCOLUMNS ( t, "I", [Index], "s", [Num Of Sales] ),
                [Index] <= [I]
            ),
            [s]
        )
    )
RETURN
    IF (
        RANKX (
            ALLSELECTED ( 'Table' ),
            CALCULATE ( SUM ( 'Table'[Num Of Sales] ) ),
            ,
            ASC,
            DENSE
        )
            >= MAXX ( FILTER ( t2, [total] >= totalsales * 0.75 ), [Index] ),
        SUM ( 'Table'[Num Of Sales] ),
        BLANK ()
    )

BTW, pbix as attached.

Best regards,

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

[Riff]

@v-lid-msft  Thank you! It works great!