Accumulated Marketshare 12 month rolling (Marketshare acumulado 12 meses moviles)

[MauricioSD]

Estimados, buenos días.

Primero que todo, agradecer por su continua ayuda.

Necesito hacer un Marketshare de varias compañías y también de varios otros segmentadores.

Tengo una tabla histórica con el dato de la fecha-monto-empresa esas serían las 3 principales columnas.

Y el marketshare se calcula como la suma del monto de los ultimos 12 meses.

Entonces el monto total de 1 empresa de diciembre-2021 es la suma de enero2021-diciembre2021.

El marketshare es el % de participación del mercado de cada empresa, que entonces el (monto de cada empresa)/(monto total)

Y por ultimo el ranking en el que se ubica según % del Marketshare.

Hoy he logrado obtener el monto acumulado de los 12 meses moviles con la formula:

Var Total =

var last_date = MAX('Calendar'[Date])

return

CALCULATE(SUM(Marketshare[CIF Total]),

DATESINPERIOD('Calendar'[Date],last_date,-12,MONTH)) 

Pero no puedo obtener el % Marketshare y tampoco el Ranking de cada empresa que pueda colocarlo en una tabla, etc.

Favor su ayuda, adjunto un excel de ejemplo como debería quedar.

[MauricioSD]

@v-yanjiang-msft 

Please your help, to be able to apply filters in the data

[v-yanjiang-msft]

Hi @MauricioSD ,

Thanks for waiting patiently! I modify the formula like this:

Total =
IF (
    DATEDIFF (
        MINX ( ALL ( Test ), Test[Month] ),
        MAXX (
            FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
            Test[Month]
        ),
        MONTH
    ) >= 11,
    CALCULATE (
        SUM ( Test[CIF Total] ),
        DATESINPERIOD (
            Test[Month],
            MAXX (
                FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
                Test[Month]
            ),
            - 12,
            MONTH
        ),
        ALLEXCEPT ( Test, Test[GRUPO MARCA], Test[Market] )
    ),
    BLANK ()
)

Marketshare (%) Test =
VAR _SUM =
    CALCULATE (
        SUM ( Test[CIF Total] ),
        DATESINPERIOD (
            Test[Date],
            MAXX (
                FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
                Test[Date]
            ),
            - 12,
            MONTH
        ),
        ALLEXCEPT ( Test, 'Test'[Market] )
    )
VAR _D =
    DIVIDE ( [Total], _SUM )
RETURN
    _D

Ranking =
IF (
    DATEDIFF (
        MINX ( ALL ( Test ), Test[Month] ),
        MAXX (
            FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
            Test[Month]
        ),
        MONTH
    ) >= 11,
    RANKX (
        FILTER (
            ALLEXCEPT ( 'Test', 'Test'[Market] ),
            'Test'[PERIODO] = MAX ( 'Date'[PERIODO] )
        ),
        CALCULATE ( [Marketshare (%) Test] ),
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

 Get the correct result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[MauricioSD]

Hi @v-yanjiang-msft 

Your solution is correct, but i have a qestion,

why are there blank spaces in some months? :

The other question, the Marketshare (%) is correct, but the Ranking no:

The 1st is correct, the 2nd no:

[v-yanjiang-msft]

Hi  @MauricioSD  ,

According to your description, here's my solution.

Create a calculated column.

Total =
IF (
    DATEDIFF (
        MINX ( ALL ( 'Marketshare' ), 'Marketshare'[Date] ),
        'Marketshare'[Date],
        MONTH
    ) >= 11,
    CALCULATE (
        SUM ( Marketshare[CIF Total] ),
        DATESINPERIOD ( 'Marketshare'[Date], 'Marketshare'[Date], - 12, MONTH ),
        ALLEXCEPT ( 'Marketshare', 'Marketshare'[Company] )
    ),
    BLANK ()
)

Create two measures.

MarketShare  (%) =
VAR _SUM =
    CALCULATE (
        SUM ( Marketshare[CIF Total] ),
        DATESINPERIOD ( 'Marketshare'[Date], MAX ( 'Marketshare'[Date] ), - 12, MONTH ),
        ALL ( Marketshare )
    )
VAR _D =
    DIVIDE ( MAX ( 'Marketshare'[Total] ), _SUM )
RETURN
    _D

Ranking =
IF (
    DATEDIFF (
        MINX ( ALL ( 'Marketshare' ), 'Marketshare'[Date] ),
        MAX ( 'Marketshare'[Date] ),
        MONTH
    ) >= 11,
    RANKX (
        FILTER (
            ALL ( 'Marketshare' ),
            'Marketshare'[Date] = MAX ( 'Marketshare'[Date] )
        ),
        CALCULATE ( SUM ( 'Marketshare'[Total] ) ),
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

Get the result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[MauricioSD]

Hi @v-yanjiang-msft 

I try with your solution, but the calculated column "Total", not work

the correct solution should be:

But the power BI with your solution is:

I attach 2 files, the excel and the pbix with complete data base.

In adition, we can filter by column "market".

Attached 

[v-yanjiang-msft]

Hi @MauricioSD,

It's because in your original sample, there is only one value in the same date and same company, but in the new sample, there are many rows, they add up in the visual, it should then divide by the number of rows.

Total =
IF (
    DATEDIFF ( MINX ( ALL ( Test ), Test[Month] ), Test[Month], MONTH ) >= 11,
    CALCULATE (
        SUM ( Test[CIF Total] )
            / COUNTROWS (
                FILTER (
                    ALL ( 'Test' ),
                    'Test'[GRUPO MARCA] = MAX ( 'Test'[GRUPO MARCA] )
                        && 'Test'[Month] = MAX ( 'Test'[Month] )
                )
            ),
        DATESINPERIOD ( Test[Month], Test[Month], - 12, MONTH ),
        ALLEXCEPT ( Test, Test[GRUPO MARCA] )
    ),
    BLANK ()
)

Get the correct result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[MauricioSD]

Hi @v-yanjiang-msft 

Thank you for your help, the "total" is correct, but why there are many blank spaces?  If it is the sum of the last 12 months

In adition, the Marketshare (%) work correctly, but the rank no:

Thanks !

[v-yanjiang-msft · Accepted solution]

Hi @MauricioSD ,

According to your new sample, here's my solution.

1.Create a new table, don't make relationship between the two tables.

Date = VALUES('Test'[PERIODO])

2.Create three measures.

Total =
IF (
    DATEDIFF (
        MINX ( ALL ( Test ), Test[Month] ),
        MAXX (
            FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
            Test[Month]
        ),
        MONTH
    ) >= 11,
    CALCULATE (
        SUM ( Test[CIF Total] ),
        DATESINPERIOD (
            Test[Month],
            MAXX (
                FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
                Test[Month]
            ),
            - 12,
            MONTH
        ),
        ALLEXCEPT ( Test, Test[GRUPO MARCA] )
    ),
    BLANK ()
)

Marketshare (%) Test =
VAR _SUM =
    CALCULATE (
        SUM ( Test[CIF Total] ),
        DATESINPERIOD (
            Test[Date],
            MAXX (
                FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
                Test[Date]
            ),
            - 12,
            MONTH
        ),
        ALL ( Test )
    )
VAR _D =
    DIVIDE ( [Total], _SUM )
RETURN
    _D

Ranking =
IF (
    DATEDIFF (
        MINX ( ALL ( Test ), Test[Month] ),
        MAXX (
            FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
            Test[Month]
        ),
        MONTH
    ) >= 11,
    RANKX (
        FILTER ( ALL ( 'Test' ), 'Test'[PERIODO] = MAX ( 'Date'[PERIODO] ) ),
        CALCULATE ( [Marketshare (%) Test] ),
        ,
        DESC,
        DENSE
    ),
    BLANK ()
)

Get the correct result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[MauricioSD]

Dear @v-yanjiang-msft 

When i filter by "Market", the numbers have no variation, for example: