How to create a simple calculated column or another workaround

[Anonymous]

Hi,

I want to do a simple operation where I get the average amount of products per person. For this I made two measures:

Productos_total =

COUNT('Clientes Mac'[Cant_CA]) +

COUNT('Clientes Mac'[Cant_CC]) +

COUNT('Clientes Mac'[Cant_TC])

This gives me the total of products per person and then:

Average productos =

AVERAGEX('Clientes Mac',[Productos_total])

To get the average. This worked in a small dataset, however, for the 10MM rows dataset it doesnt work. I would prefer to just get the average of Productos_total but that's not possible since that is a measure.

My question is: Is there another way? Could someone give me a code to create a calculated column?

Thanks in advance,

IC

[Nathaniel_C]

Hi @Anonymous ,

To only include customers who own at least 1 product. Products must be represented in the Data (Sales) table

If this solves your issue, please consider marking my reply as the solution.

Thanks,

@Nathaniel_C  

[Anonymous]

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 ,

Thinking about this a little further, the functions don't change based on rows, except for two possibilities that spring to mind.

One there is some bad data, or possibly unexpected data in your column. As the number that you reported 

"It returns around 246,456. I assume it does a count per row and then a sum among rows." is very large, it is possible that there is a large number in your data set. You can sort that column in descending order, or do a number filter for greater than some expected limit as in the picture below. (I used 100, and the large number showed up in the table

The other possibility that I can think of is that dataset is too large for your system, if you are importing it. In which case it may be including a sum of the remaining rows.  Don't know how that function works, though.

If this solves your problem, please consider marking this as a solution,

@Nathaniel_C 

[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 use 

Average 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,

@Nathaniel_C 

[Anonymous]

Hi,

Really appreciate all your efforts however, as you said,  this gives you the average by purchase order, not by customer as I requested.

Nonetheless, I figured it out. I did the following:

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])

and then:

Promedio de Productos por persona =

AVERAGEX(

    KEEPFILTERS(VALUES('Data clientes lunes por 6 meses'[Codigo_Cliente])),

    CALCULATE([Productos por persona])

)

Thanks for your help!

[Nathaniel_C]

Hi @Anonymous ,

You are welcome!

So I have a question, a suggestion and an observation.

Does your solution work on your large dataset?

My suggestion is that you might use 

[Average Productos 2] = DIVIDE(SUM('Clientes Mac'[Product Count]),COUNTROWS(VALUES('Clientes Mac'[Client])))

which might be quicker than using AVERAGEX() on a Data table. 

Finally looking at your Productos_total:

Productos_total =

COUNT('Clientes Mac'[Cant_CA]) +

COUNT('Clientes Mac'[Cant_CC]) +

COUNT('Clientes Mac'[Cant_TC])

gives you a total of 3 in your first row of the original data. So doing it this way counts the number of categories not the number of products purchased.

For clarification, if we call the columns Pencils, Pens, Markers and the values are 1,2,3, The customer will have 1 Pencil, 2 Pens, and 3 Markers, which is 6 products. However if you use Count you will get 1 for each category or a total of 3 because she has purchased something in each category.

If she comes back another day, as we see on the second line of the original data that you provided, the values are 1, 2. So now she has a total of 2 Pencils, 4 Pens, and still 3 Markers. for a total of 9 products.  However she still has only 3 categories, since all categories were marked in the first line. The way it is currently set up, it will be showing 5 categories.

Does this make sense?

So if your aim is to show average number of products per customer rather than categories per customer, then you might want to think about using sum of your column 'Clients Mac'[Product QTY] from your original table.

[Average Productos 2] = DIVIDE(SUM('Clientes Mac'[Product QTY]),COUNTROWS(VALUES('Clientes Mac'[Client])))

If you want to use categories, you will have more work to do.

If you think this is the solution, you may want to mark this as such.

@Nathaniel_C 

[Anonymous]

Hi @Nathaniel_C 

Thanks for the info. Way faster, however, I dont know which number is correct.

At the moment, Im using:

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]))

)

The numbers are extremely different and I dont know which one is correct. I will have to try to get a sample. Looking at the logic, do you think there could be an error?

Thanks in advance,

IC

@Ashish_Mathur 

[Nathaniel_C · Accepted solution]

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:

 

 and 

So 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,

@Nathaniel_C

[Anonymous]

Hi @Nathaniel_C .

You were very helpful. Everything has been solved. 

Have a nice day,

IC

[Nathaniel_C]

Hi @Anonymous ,

My pleasure!

In order to facilitate our conversation, would you please use 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:

 and 

So looking at our original data, would you expect the count of products to be 16, or 21?

I will post some more, but this may get you started.

Thank you,

@Nathaniel_C 

[Ashish_Mathur]

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.

[Anonymous]

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]

@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,

@Nathaniel_C 

[Anonymous]

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.

[vanessafvg]

@Anonymous  when you say it doesn't work what happens?  

[Anonymous]

When I do it, it returns the same result for every row.

[HotChilli]

Does it return 3 for every row?

[Anonymous]

It returns around 246,456. I assume it does a count per row and then a sum among rows.