Forum Discussion

SebastianY's avatar
SebastianY
Regular Visitor
3 years ago
Solved

Problem with TOPN

Hello everyone,   I'm trying to create a formula that groups all customers whose purchases represent less than 1% of total sales (later on, I would like to make that 1% vary with a parameter, but I...
  • SebastianY's avatar
    SebastianY
    3 years ago

    Hello

     

    Ok, I did a mix between your code and what I had and I think the problem is finally resolved.

    I must confess that I don't know well why one code works and the other doesn't, but ok.

     

    Thank you so much for your help. This couldn't have been possible without your suggestions.

     

    I'll put the code so that anyone with the same problem can see it.

     

    Ventas con Otros =
    VAR PorcentajeElegido = DIVIDE(Porcentaje[Valor de Porcentaje],100)
    VAR PorcVentas = CALCULATE([TotalVentas], ALLSELECTED ( 'Clientes con Otros' ) ) * PorcentajeElegido
    VAR TablaResumen =
        ADDCOLUMNS(
            SUMMARIZE (ALL(VENTAS),
            'Clientes con Otros'[Nombre]),
            "VentaCliente", [TotalVentas]
        )
    VAR TablaResumenFiltrada =
        FILTER ( TablaResumen, [VentaCliente] > PorcVentas )
    VAR VentasTopN =
        SUMX ( TablaResumenFiltrada, [VentaCliente] )
    VAR VentasTodos =
        SUMX(TablaResumen,[TotalVentas])
    VAR VentasOtro = VentasTodos - VentasTopN
    VAR EsOtroSeleccionado =
        SELECTEDVALUE ('Clientes con Otros'[Nombre]) = "Otros clientes"
    VAR resultado =
        IF (
          ISINSCOPE ( 'Clientes con Otros'[Nombre] ),
           IF (
               EsOtroSeleccionado,
               VentasOtro,
               IF(
                   [TotalVentas]>=PorcVentas,[TotalVentas])
           ),
           [TotalVentas]
        )
    Return
    resultado