cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Custom Column - Count of products per client over time

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

IC

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Custom Column - Count of products per client over time

Hi @icespedes ,

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

Please mark my reply as a solution if it works. Thank you!
2 REPLIES 2
Frequent Visitor

## Re: Custom Column - Count of products per client over time

Hi @icespedes ,

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

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

## Re: Custom Column - Count of products per client over time

Hi @tatasherry ,

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.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 105 members 1,930 guests
Recent signins: