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

     

     

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

I don't think you need TOPN at all. You already have the filtered table containing the sales amount, you can simply do a SUMX over that. 

I also changed the SUMMARIZE to use ADDCOLUMNS, its best practice to do that rather than adding calculated columns using SUMMARIZE itself

Ventas con Otros =
VAR PorcVentas = [TotalVentas] * 0.01
VAR TablaResumen =
    ADDCOLUMNS (
        SUMMARIZE ( VENTAS, 'Clientes con Otros'[Nombre] ),
        "VentaCliente", [TotalVentas]
    )
VAR TablaResumenFiltrada =
    FILTER ( TablaResumen, [VentaCliente] > PorcVentas )
VAR VentasTopN =
    SUMX ( TableResumenFiltrada, [VentaCliente] )
VAR VentasTodos =
    CALCULATE ( [TotalVentas], ALLSELECTED ( 'Clientes con Otros' ) )
VAR VentasOtro = VentasTodos - VentasTopN
VAR EsOtroSeleccionado =
    SELECTEDVALUE ( 'Clientes con Otros'[Nombre] ) = "Otros clientes"
RETURN
    IF (
        ISINSCOPE ( 'Clientes con Otros'[Nombre] ),
        IF ( EsOtroSeleccionado, VentasOtro, VentasTopN ),
        VentasTodos
    )
  • SebastianY's avatar
    SebastianY
    Regular Visitor
    3 years ago

    First of all, thank you for your response.

    Second, in fact, what you propose is a path that I tried, and it does seem more logical. The problem here is that I don't know at which exact point the issue is generated in such a way that "Other customers" always receives the same value: the total sum of sales. If I use this information in a pie chart, "Other customers" always represents 50%, no matter if I change the formula of PorcVentas and multiply it by 0.0000001 or by 0.5.

    If it helps, let me share the formula for the measure [TotalVentas] = SUM(SALES[Amount]).

     

    EDIT:

     

    I will provide further information. I conducted a test for a 20% percentage, then assigned this variable to a card and checked how well the VentasTodos, VentasTopN, and VentasOtros variables were functioning. I performed the calculations separately in an Excel sheet, and I can assure you that those variables provide the correct values. Therefore, I assume that the issue lies in how the value of VentasOtros is assigned to the "Otros clientes" row (the IF statement at the end of the formula). It seems that instead of assigning that variable, the VentasTodos variable is being assigned.

     

    I also tried something else; I replaced the VentasTodos variable with an integer (formula below). And the value was assigned correctly.

        IF (
          ISINSCOPE ( 'Clientes con Otros'[Nombre] ),
           IF ( EsOtroSeleccionado, 10000000, VentasTopN ),
            VentasTodos
       )
     
    Pie chart:
    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      What is the 'Clientes con Otros'[Nombre] column and how is that calculated? How is it related to Sales ?

      • SebastianY's avatar
        SebastianY
        Regular Visitor
        3 years ago

        It's a calculated table:

         

        Clientes con Otros =
            UNION(
                ALLNOBLANKROW(CLIENTES[Nombre]),
                {"Otros clientes" }
                )
         
        And here's the relationship:
         

         

        By the way, I've edited the previous replay with futher information.