Solved: How to create a simple calculated column or anothe... - Page 2

Anonymous · ‎08-07-2019

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 · ‎08-09-2019

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

View solution in original post

How to create a simple calculated column or another workaround

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly