Forum Discussion
How to create a simple calculated column or another workaround
This is a second post of this message...not sure where the first went. Please read this one.
Hi @icespedes ,
My pleasure!
In order to facilitate our conversation, would you please you the following syntax?
[measure name] = blah,blah,blah
tablename [Column name] = and so forth
This is common usage, and lets the other person know whether they are looking at a measure or a calculated column.
Thank you!
So, I took what you presented and applied it to the original data that you gave us as follows:
"Promedio de Productos por persona =CALCULATE(DIVIDE([Productos por persona],COUNTROWS('Data clientes lunes por 6 meses'),"No Result"),KEEPFILTERS(VALUES('Data clientes lunes por 6 meses'[Codigo_Cliente])))"Modified to fit the original data and my modified table Clients Mac new: (Assuming this is a measure)[Promedio de Productos por persona] =CALCULATE(DIVIDE([Productos por Persona],COUNTROWS('Clientes Mac new'),"No Result"),KEEPFILTERS(VALUES('Clientes Mac new'[Client])))And
"Productos por persona =COUNT('Data clientes lunes por 6 meses'[Cant_CA]) +COUNT('Data clientes lunes por 6 meses'[Cant_CC]) +COUNT('Data clientes lunes por 6 meses'[Cant_TC]) +COUNT('Data clientes lunes por 6 meses'[Cant_CD]) +COUNT('Data clientes lunes por 6 meses'[Cant_NE]) +COUNT('Data clientes lunes por 6 meses'[Cant_PR_Vh]) +COUNT('Data clientes lunes por 6 meses'[Cant_PR_Com]) +COUNT('Data clientes lunes por 6 meses'[Cant_PR_Hip]) +COUNT('Data clientes lunes por 6 meses'[Cant_PR_Tie]) +COUNT('Data clientes lunes por 6 meses'[Cant_PR_Per]) +COUNT('Data clientes lunes por 6 meses'[Cant_PR_LC])"to[Productos por Persona] = COUNT('Clientes Mac new'[Cant_CA])+ COUNT('Clientes Mac new'[Cant_CC])+COUNT('Clientes Mac new'[Cant_TC])So, would you agree that these are both measures and equivalent? If not we should discuss.So based on your latest and applied to our original data, I get:andSo looking at our original data, would you expect the count of products to be 16, or 21?And looking at our original data, would you expect the count of unique clients to be 4?I will post some more, but this may get you started.Thank you,
18 Replies
- vanessafvg
Community Champion
Anonymous when you say it doesn't work what happens?
- HotChilli
Does it return 3 for every row?
- Nathaniel_C
Anonymous ,
How are you planning to use this? Will a measure work? Are you putting this on a dashboard? Or do you need a CColumn. Not sure, because you said "...or some other workaround?"
Do you have a products lookup table, a customer lookup table, and a sales data table?
Thanks,
Hi Nathaniel_C ,
I think the best way to do it would be creating a CColumn. What I need is the average amount of distinct products per person. This means that I need the amount of distinct products for each customer and then make an average.
- Ashish_Mathur
Super User
Hi,
You say that your solution worked on a small dataset but not on a large one. That's surprising. There is another solution you can try though i am not sure of whether it will work on a 10 million row dataset. You could select all columns other than the 3 columns (which you are referring to in your measure) and select "Unpivot other columns". Now you could use the Average function on the single numeric column.
Hi Ashish_Mathur ,
This would convert my table in a more than 10MM row dataset, and will destroy all my other calculations. I dont think this would be the best way.
- Nathaniel_C
Hi Anonymous ,
To only include customers who own at least 1 product. Products must be represented in the Data (Sales) tableIf this solves your issue, please consider marking my reply as the solution.
Thanks,
Hi Nathaniel_C Ashish_Mathur ,
It will not work since everything is in the same table. At the moment I have something like this:
Actual
What I need is simple, using these formulas:
Calculation in excel
In order to get this:
Desired
It worked on a small dataset but it didnt in a large one with these formulas:
Productos_total =COUNT('Clientes Mac'[Cant_CA]) +COUNT('Clientes Mac'[Cant_CC]) +COUNT('Clientes Mac'[Cant_TC])Average productos =AVERAGEX('Clientes Mac',[Productos_total])Probably because AVERAGEX goes row by row. Therefore, I need a better solution for this simple calculation.- Nathaniel_C
Hi Anonymous ,
Thank you for the additional information, it is always helpful! (You can click on the following pics to get a larger version.)
So, I created in Power BI the following table.
Product Count = if('Clientes Mac'[Cant_TC]<>0,1,0)+if('Clientes Mac'[Cant_CC]<>0,1,0)+if('Clientes Mac'[Cant_CA]<>0,1,0)The last column that I created is by this formula:Average Items per Purchase = DIVIDE('Clientes Mac'[Product QTY],'Clientes Mac'[Product Count])I also followed along by seeing what your example in Excel.
As you know, the average is the total / count. Thus in the Excel, we can either use Average(), or =16/12 (Sum of column/ by row count.
If you wish to duplicate this in PBI, you can use the following measure.
Average Productos 2 = DIVIDE(SUM('Clientes Mac'[Product Count]),COUNTROWS('Clientes Mac'))Which totals the product count, counts the number of rows in the table, and divides the first by the second.Please note, this matches the Excel file average.Now that it is proven mathmatically, you can useAverage of Product Count = AVERAGE('Clientes Mac'[Product Count]) which is simpler.Please note: This gives you the same total as what you were looking for in Excel. However this gives you the average by purchase order, not by customer as I believe you requested. I note in your example that each customer may purchase on different fechas. So your description, and your Excel example vary.Hope this helps, and if it does, please consider marking it as the solution,
