Custom Column - Count of products per client over time

[Anonymous]

Hi,

I have several calculations that I want to perform. My data looks like this at the moment:

Current data

The first thing that I want is to create a Custom Column using Edit Queries. I want a Product count per product (Products being Cant_TC / Cant_CC / Cant_CA). Afterwards, I would like a measure that gives me the average product count per client. I did it this way in Excel:

Desired measures

In the end, the goal is to get something like this:

Goal

It is important to know that this calculations will change over time so it must include some kind of client and time measure (FechaCarga and Client).

Thanks in advance,

IC

[Anonymous · Accepted solution]

Hi @Anonymous ,

There are 3 measures you need:

Product Qty = SUM(Sheet1[Cant_CA]) + SUM(Sheet1[Cant_CC]) + SUM(Sheet1[Cant_TC])

Product count = COUNT(Sheet1[Cant_CA]) +COUNT(Sheet1[Cant_CC]) + COUNT(Sheet1[Cant_TC])

Average Products = AVERAGEX(Sheet1, [Product count])

I also upload the test file I made, download it and you can run it on your computer:

https://drive.google.com/file/d/1whLG5fuazBSjvOSyfzM4G7OLg6aXRK_2/view?usp=sharing

Please mark my reply as a solution if it works. Thank you!

[Anonymous]

Hi @Anonymous ,

I had something similar: 

Average products = 

VAR productos_total =

COUNT('Clientes Mac'[Cant_CA]) +

COUNT('Clientes Mac'[Cant_CC]) +

COUNT('Clientes Mac'[Cant_TC]) +

COUNT('Clientes Mac'[Cant_CD]) +

COUNT('Clientes Mac'[Cant_NE]) +

COUNT('Clientes Mac'[Cant_PR_Vh]) +

COUNT('Clientes Mac'[Cant_PR_Com]) +

COUNT('Clientes Mac'[Cant_PR_Hip]) +

COUNT('Clientes Mac'[Cant_PR_Tie]) +

COUNT('Clientes Mac'[Cant_PR_Per]) +

COUNT('Clientes Mac'[Cant_PR_LC])

return

AVERAGEX('Clientes Mac'[Productos_total])

and it didnt work. Now I tried using two separate measures and it worked. I dont understand why but thanks.