cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
icespedes Regular Visitor
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:

 

image.pngCurrent 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:

image.pngDesired measures

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

image.pngGoal

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
tatasherry Frequent Visitor
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])
 
I also upload the test file I made, download it and you can run it on your computer:
 
Please mark my reply as a solution if it works. Thank you!
2 REPLIES 2
tatasherry Frequent Visitor
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])
 
I also upload the test file I made, download it and you can run it on your computer:
 
Please mark my reply as a solution if it works. Thank you!
icespedes Regular Visitor
Regular Visitor

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

Hi @tatasherry ,

 

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 105 members 1,930 guests
Please welcome our newest community members: