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

     

     

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.

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

      It seems to me that when 'Clientes con Otros'[Nombre] is filtered to "Otros clientes" that would result in an empty Sales table. Can you check by returning COUNTROWS(TablaResumen) in a visual with 'Clientes con Otros'[Nombre]? 

      • SebastianY's avatar
        SebastianY
        Regular Visitor
        3 years ago

        I dont know if I got you correctly

         

        If I put the meassure in a cart, it shows 2, which is correct, and it changes if I cange the formula for PorcVentas.

        Then I put it in a table, this is the result:

         

         

        Now, If I look for "Otros clientes" in the table, it's not there



        EDIT:
        Sorry, I used TablaResumenFiltrada in COUNTROWS instead of TablaResumen.