Define median of grouped and non-recurring data

[Syndicate_Admin]

Friends, how are you doing?

I have the following problem and I would like to have your help.

I have a base where you have the Id_cliente, the code of a product that the customer bought and the amount that he bought. In many cases the customer repeats the purchase of that product, although sometimes he does it with different quantities.

I am needing to determine the median of each product consumed (code), for which I first group it to the code per client so that it adds the total of each one, and then I apply the median. The problem I have with that function is that it only returns the median of those customers who had consumption, and if the client did not consume that product does not take it into account, when I should put value 0.

I share capture, to see if they understand a little more.

Base original

Base originalBase original

Product grouping by customer

Agrupando producto por cliente. Me calcula la mediana sobre esos valores.Grouping product by customer. It calculates the median on those values.

Intended result

Formato que necesito.Format I need.

Can you think of a solution!?

M

[Syndicate_Admin]

ArchivoPBI

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Ensure that you already have the table/matrix there already.

[Syndicate_Admin]

Nice day. Attached the file

Archivo PBI

I need a table where if the product was not consumed put a 0, having that could determine the median correctly.

Thanks a lot!

[Ashish_Mathur]

Hi,

I get an error when i click on that link.

[Syndicate_Admin]

Back, I just checked that the previous one didn't go up. Thank you

PBI File

[Ashish_Mathur]

See if this measure works

Mediana_cantidad 1 = MEDIANX(generateall(values(Tabla_precio[Código]),values(Tabla_precio[PRECIO UNITARIO ÚLTIMA COMPRA])),min('Consulta_consumo'[Cantidad_]))

[Syndicate_Admin]

Attach back the link, to see if it can now be viewed

ArchivoPBI

Thanks a lot