Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MauricioSD
Helper I
Helper I

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

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_0-1646750941478.png

 

1 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.

vkalyjmsft_0-1647598659149.png

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.

 

View solution in original post

9 REPLIES 9
MauricioSD
Helper I
Helper I

@v-yanjiang-msft 

 

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

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.

vkalyjmsft_0-1648005029144.png

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
Helper I
Helper I

Hi @v-yanjiang-msft 

Your solution is correct, but i have a qestion,

why are there blank spaces in some months? :

MauricioSD_0-1647353084831.png

 

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

The 1st is correct, the 2nd no:

MauricioSD_1-1647353191280.png

 

 

 

v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1646988025968.png

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.

 

Hi @v-yanjiang-msft 

 

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

 

the correct solution should be:

MauricioSD_0-1647025453032.png

 

But the power BI with your solution is:

MauricioSD_1-1647025526655.png

 

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

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

Attached 

 

 

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.

vkalyjmsft_0-1647324020850.png

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.

vkalyjmsft_1-1647324391846.png

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.

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

MauricioSD_0-1647440519767.png

 

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

MauricioSD_1-1647440618789.png

 

Thanks !

 

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.

vkalyjmsft_0-1647598659149.png

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.

 

Dear @v-yanjiang-msft 

 

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

 

MauricioSD_0-1647614534762.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors