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'll solve that later). I have tried to solve this problem in various ways, and one of them is summarizing the table with my customers, filtering them by their sales level, counting the rows, saving that information in a variable, and using it as the value of N in the TOPN formula. But it doesn't work.

 

Specifically, it doesn't generate any error message, the syntax, in that sense, is correct. It's as if TOPN simply doesn't fulfill its function, it doesn't select the number of rows that the variable represents. I have tried transferring all that information to a measure and using it in TOPN, but it doesn't work either.

 

I have individually evaluated each part of the formula and confirmed that it works correctly. In fact, if I replace the variable in the TOPN formula with an integer, then the formula as a whole works perfectly. Does anyone know why?

I'm from Spain, so the variables are in Spanish. I hope it's not a problem to understand the code.

 
Ventas con Otros =

VAR PorcVentas = [TotalVentas] * 0.01

VAR TablaResumen =
SUMMARIZE(VENTAS, 'Clientes con Otros'[Nombre], "VentaCliente", [TotalVentas]
)

VAR TablaResumenFiltrada =
    FILTER(TablaResumen, [VentaCliente] > PorcVentas)

VAR ValorN=
INT(
    COUNTROWS(TablaResumenFiltrada)
)

VAR TopNTabla=
TOPN(
    ValorN,
    ALLSELECTED('Clientes con Otros'),
    [TotalVentas]
)

VAR VentasTopN=
CALCULATE(
    [TotalVentas],
    KEEPFILTERS(TopNTabla)
)

Var VentasTodos=
CALCULATE(
    [TotalVentas],
    ALLSELECTED('Clientes con Otros')
)

Var VentasTodosTopN=
CALCULATE(
    [TotalVentas],
    TopNTabla
)

VAR VentasOtro = VentasTodos - VentasTodosTopN

VAR EsOtroSeleccionado = SELECTEDVALUE('Clientes con Otros'[Nombre]) = "Otros clientes"

RETURN

IF(
    ISINSCOPE(
        'Clientes con Otros'[Nombre]),
        IF(EsOtroSeleccionado,VentasOtro,VentasTopN),VentasTodos)

 

 

By the way, I imagine there are more efficient ways to solve this problem, such as using the summarized and filtered table directly instead of counting its rows and evaluating that value in TOPN, but the truth is that system didn't work for me either.

Thanks

  • 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

     

     

16 Replies

  • johnt75's avatar
    johnt75
    Super 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
        Super User
        3 years ago

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