Measure for most recurring value

[joerykeizer]

Hi all,

I have a customer table and a purchase order table. 

For each customer I would like to create a measure showing which producttype they prefer. E.g. if Customer X makes 5 orders, 3x type A and 2x type B, I need the measure to return value A. I would also like to be able to calculate the 60% likelihood of them buying a product of type A (this can be a seperate measure). 

Because I'll need to do this for a lot of different product characteristics I'd prefer not having to make additional tables.

Anyway this is possible?

[joerykeizer]

@v-haibl-msft

Got it! Thank's again. Learning the VAR functionality was especially helpful.

[joerykeizer]

@v-haibl-msft Awesome solution, thank you very much.

A follow up question if I you don't mind. Now I run into a 'problem' when there are two product types with the same value. It shows the preference as "A, B" but there is no real product type "A, B" so the likelyhood is empty.

Is it possible to show only 1 product type based on another column (date). So if it's 50/50 it would pick the most recent one. Or if this is easier: keep the "A, B" but return the percentage of both individual types? 

[v-haibl-msft]

@joerykeizer

Please try to change the formula of Prefered_Product with nested TOPN as below.

Prefered_Product = 
CONCATENATEX (
    TOPN (
        1,
        TOPN (
            1,
            SUMMARIZE (
                'Purchase Order',
                'Purchase Order'[Customer],
                'Purchase Order'[Product],
                'Purchase Order'[Date],
                "Number", CALCULATE (
                    COUNTROWS ( 'Purchase Order' ),
                    ALLEXCEPT (
                        'Purchase Order',
                        'Purchase Order'[Customer],
                        'Purchase Order'[Product]
                    )
                )
            ),
            [Number]
        ),
        'Purchase Order'[Date]
    ),
    'Purchase Order'[Product]
)

Best Regards,

Herbert

[v-haibl-msft · Accepted solution]

@joerykeizer

Please try with following two measures.

Prefered_Product = 
CONCATENATEX (
    TOPN (
        1,
        SUMMARIZE (
            'Purchase Order',
            'Purchase Order'[Customer],
            'Purchase Order'[Product],
            "Number", COUNTROWS ( 'Purchase Order' )
        ),
        [Number]
    ),
    'Purchase Order'[Product], ", "
)

Likehood = 
VAR Prefered_Product =
    CONCATENATEX (
        TOPN (
            1,
            SUMMARIZE (
                'Purchase Order',
                'Purchase Order'[Customer],
                'Purchase Order'[Product],
                "Number", COUNTROWS ( 'Purchase Order' )
            ),
            [Number]
        ),
        'Purchase Order'[Product],
        ", "
    )
RETURN
    (
        DIVIDE (
            CALCULATE (
                COUNTROWS ( 'Purchase Order' ),
                FILTER ( 'Purchase Order', 'Purchase Order'[Product] = Prefered_Product )
            ),
            COUNTROWS ( 'Purchase Order' )
        )
    )

Best Regards,

Herbert

[Anonymous]

@v-haibl-msft

Hi, one question, what does 'NUmber" stands for? I am trying to appl your dax function, but i don´t know waht to put i "Number" because if I put "Number", it appear in Read, as well as the ", ".

I want to do the same but to show the average marital status of my employees.

My Data is located in "Data", the employees in "Name", and the martial Status in "Civil Status".

The function I am doing is: 

Recurrent Civil Status = CONCATENATEX(TOPN(1,SUMMARIZE(Data,Data[Name],Data[Civil status]),COUNTROWS(Data),1),Data[Civil status],1)

thank you very much!

[Vvelarde]

@Anonymous

Hi, please share sample data and expected result and maybe we can help in a better way

Regards

[Anonymous]

Hola! Creo que hablas español, te explico. Necesito que aparezca lo siguiente:

Cual es el Estado Civil más repedito, casado, soltero, etc. 

Lo que estoy armando es un reporte del trabajador promedio, y quiero colocar la edad promedio, tiempo de servicio promedio, etc. y uno de los campos es el estado civil promedio de mi población. 

Lo mismo quiero hacer del género, distrito donde vive, cantidad de hijos, etc.

Name	Marital Status
ALEGRIA SOTO	CASADO (A)
ABAD LLACSAHUAN	CASADO (A)
ANCORI BUSTAMANT	SOLTERO (A)
BOJORQUEZ VA	CASADO (A)
BRUNA ALV	SOLTERO (A)
ABANTO GIL GRE	SOLTERO (A)
CASTILLO DIAZ	CASADO (A)
ABANTO PAREDES DIA	SOLTERO (A)
CHANCOS ALLCA ANTH	SOLTERO (A)
ABANTO VERTIZ LI	SOLTERO (A)
CHILO REA LILIA	SOLTERO (A)
GARCIA FERNANDEZ FRED	CASADO (A)
GOMEZ VELARD	CASADO (A)
ACERO ROMERO DAN	SOLTERO (A)
ACEVEDO GEREDA MIGU	SOLTERO (A)
ACOSTA BARREDA JUNI	SOLTERO (A)
GUEVARA CORRALES JORG	SOLTERO (A)
GUZMAN HERRERA ENRI	CASADO (A)
HERRERA ZENTENO SAND	CASADO (A)
ORTEGA R	SOLTERO (A)
PRIETO LAND	SOLTERO (A)
QUIROZ RIVERO J	CASADO (A)
AGUILAR MEDI	CONVIVIENTE
AGUILAR MEJICO ELI	SOLTERO (A)
AGUILAR RO	SOLTERO (A)
AGUILAR SALINA	CASADO (A)
RIVAS PACHECO VICT	SOLTERO (A)
RUIZ DIAZ	SOLTERO (A)

[Vvelarde]

@Anonymous

The easiest way i think is this:

A table Visual and this fields and apply a TOPN

[Vvelarde]

@Anonymous

If you want a Dax Formula you can use this:

Measure =
CONCATENATEX (
    TOPN (
        1,
        SUMMARIZECOLUMNS (
            Table1[Marital Status],
            "Contar", CALCULATE ( COUNT ( Table1[Marital Status] ) )
        ),
        [Contar]
    ),
    Table1[Marital Status],
    "."
)

[Anonymous]

And what does Contar stands for? beacuse it appears in red. i put between [] and it appear the following:

[Vvelarde]

In this case contar is the name of the colum created to store the count of each marital status. This column after is call to use to elaborate the Top1. The first is with " and the second is with []

[Anonymous]

I have to create this column then, in my excel? or in Powwer BI? what should I put?

thanks a lot!! @Vvelarde

[Vvelarde]

@Anonymous

Is not necessary created a new column the dax formula create temporaly.

Look this is the dax for your data:

Maybe you need to replace ; by , and Table1 by your table name.

[joerykeizer]

Thank you for your responses but I don't think the solution is there. The problem is that I don't want to show all product types but only return the one that occurs the most. The result should be 1 row per customer. 

So not:

Customer X - Product type A - 3x - 60%

Customer X - Product type B - 2x - 40%

But rather:

Customer X - Type A - 60%

I guess I could use RankX to rank by Customer/ProductType and only show Rank=1, but given my experiences with RankX so far it get's really slow with large amounts of data, so I prefer not to use it in this case. Is there something else?

[BhaveshPatel]

If Possible, Send me an email to  -- ptelearningworld@gmail.com with your data and requirements.

[BhaveshPatel]

Hi there,

For your question 1:

You can use CALCULATE  for counting only Prouduct A or Product B

TOTAL PRODUCTS:=COUNTROWS(PRODUCTS)

PRODUCT A:=CALCULATE([TOTAL PRODUCTS), FILTER(PRODUCTS, PRODUCTS[PRODUCTNAME]="PRODUCT A")

Similarly

 Change the last part of the DAX Measure and use PRODUCT B.

For Calculating the chances of Buying Product B with Product A,

Please refer to the post here:

http://www.daxpatterns.com/basket-analysis/

This will help to answer the Part B of your Question.

[Greg_Deckler]

For the other measure, you would use a formula like:

Percent = COUNT([Order])/CALCULATE(COUNT([Order]),ALL(Orders))

And then put that measure in a table along with your "Product Type"

[Greg_Deckler]

I would suggets using RANKX: https://msdn.microsoft.com/en-us/library/gg492185.aspx

[Habib]

You should use RANK function based on Product Type Count and group by Customer and Product Type. This will give you a summarized table having information for each customer and then you can adjust your ranking to get likelihood of purchase