Forum Discussion
Measure for most recurring value
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?
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
19 Replies
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
I would suggets using RANKX: https://msdn.microsoft.com/en-us/library/gg492185.aspx
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"
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.
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?
If Possible, Send me an email to -- [email protected] with your data and requirements.
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
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!
Anonymous
Hi, please share sample data and expected result and maybe we can help in a better way
Regards
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?
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
