Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
@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
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
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) |
@Anonymous
The easiest way i think is this:
A table Visual and this fields and apply a TOPN
@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], "." )
And what does Contar stands for? beacuse it appears in red. i put between [] and it appear the following:
I have to create this column then, in my excel? or in Powwer BI? what should I put?
thanks a lot!! @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.
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 -- ptelearningworld@gmail.com with your data and requirements.
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.
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"
I would suggets using RANKX: https://msdn.microsoft.com/en-us/library/gg492185.aspx
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |