Problem with TOPN

[SebastianY]

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

[johnt75]

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]

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]

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

[SebastianY]

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]

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]

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.

[johnt75]

I think that's the problem. You need to change it to

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

[SebastianY]

Ok, this is the current formula:

Ventas con Otros =

VAR PorcVentas = [TotalVentas] * 0.2

VAR TablaResumen =

    SUMMARIZE (

        ALL(VENTAS),

        'Clientes con Otros'[Nombre],

        "VentaCliente", [TotalVentas]

    )

VAR TablaResumenFiltrada =

    FILTER ( TablaResumen, [VentaCliente] > PorcVentas )

VAR dato=

COUNTROWS(TablaResumen)

VAR VentasTopN =

    SUMX ( TablaResumenFiltrada, [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

   )

and the pie chartt is like this:

And, of course, it makes no sence.

I checked the values of VentasTodos, VentasTopN and VentasOtro variables and they show the correct number.

"Otros clientes" has 0, althought it appears in the table, at least. And again, if I replace the VentasOtros variable with an interger, then the value is assigned correctly.

However, I feel like we are very close.

[johnt75]

Is the FILTER the right way round ? It is filtering for people with more than 20%, should it be filtering for less than 20% ?

[SebastianY]

Correct, I want to single out those with sales higher than 20%, and group the rest.

[johnt75]

I think I've got it. PorcVentas isn't being calculated correctly, its using the sales for the currently selected customer, not all the customers. It should be

VAR PorcVentas = CALCULATE([TotalVentas], ALLSELECTED ( 'Clientes con Otros' ) ) * 0.2

[SebastianY]

Hello Johny75

I dont know if the problem was there. I did the change you suggested, and now every customer has exactly the same sales value.

If it helps, that value is equivalent to the sum of the customers who meet the condition of being above 20% of sales.

On the other hand, I just figured out that external filters don't change VentasTopN values and I want that. If I filter by a month, for example, I want the 20% of sales to be calculated based on the sales of that specific month, the same goes for VentasTopN, etc.

[johnt75]

In TablaResumn you're using ALL(VENTAS), that will remove any filters. You'll need to tweak that to get the right combination of which filters you want to keep and which, if any, you want to remove.

[SebastianY]

Ok, I replaced it with KEEPFILTERS:

Ventas con Otros =

VAR PorcVentas = CALCULATE([TotalVentas], ALLSELECTED ( 'Clientes con Otros' ) ) * 0.2

VAR TablaResumen =

    SUMMARIZE (KEEPFILTERS(VENTAS),

        'Clientes con Otros'[Nombre],

        "VentaCliente", [TotalVentas]

    )

VAR TablaResumenFiltrada =

    FILTER ( TablaResumen, [VentaCliente] > PorcVentas )

VAR VentasTopN =

    SUMX ( TablaResumenFiltrada, [VentaCliente] )

VAR VentasTodos =

    CALCULATE ( [TotalVentas], KEEPFILTERS( '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

   )

The only bad thing is there is no "Otros clientes"

For the 20% of sales this is correct - only those have more than 20% of sales.

Let's see, I have been able to verify that the entire formula works. I have individually evaluated each variable, and they work correctly with filters, displaying the corresponding values. The problem lies in the last part of the formula, from "Return" onwards. Power BI doesn't "want" to assign the value of the variable "VentasOtro" to the "Otros clientes" row, and I don't understand why. Because if I replace "VentasOtro" with any value, then it is assigned correctly. I have used "VentasOtro" in a visualization, and it works fine. I have also used "EsOtroSeleccionado" in a table along with 'Clientes con otro'[Nombre], and it effectively generates a column where all the values are False except for "Otros clientes," which has True.

EDIT:

I don't know if this can be useful, but I came up with an alternative method. The difference is that instead of choosing the percentage under which I want to group the customers, you choose how many customers you want to see and group the rest (that's why I initially made this formula with TOPN). The final part is the same. But in this formula, it does work.

Ventas con Otros =

VAR Parametro =

WITopN[Valor de WITopN]

VAR TotalClientes = [Cant clientes esp]+[Cant clientes no esp]

VAR TopNTabla=

TOPN(

    Parametro,

    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)

[johnt75]

I have a working version of the code,

VAR BoundaryAmount =
	CALCULATE( [Amount], ALLSELECTED( 'IDs and others' ) ) * 0.2
VAR CurrentAmount = [Amount]
VAR SummaryTable =
	ADDCOLUMNS(
		SUMMARIZE( ALL( 'Table' ), 'IDs and others'[Index] ),
		"@amount", [Amount]
	)
VAR TopSellers =
	FILTER( SummaryTable, [@amount] >= BoundaryAmount )
VAR AmountTopSellers = SUMX( TopSellers, [@amount] )
VAR TotalAmount = SUMX( SummaryTable, [@amount] )
VAR AmountOthers = TotalAmount - AmountTopSellers
VAR OthersSelected =
	SELECTEDVALUE( 'IDs and others'[Index] ) = "Others"
VAR Result =
	IF(
		ISINSCOPE( 'IDs and others'[Index] ),
		IF(
			OthersSelected,
			AmountOthers,
			IF( CurrentAmount >= BoundaryAmount, CurrentAmount )
		),
		TotalAmount
	)
RETURN
	Result

The only thing which would need tweaking is the ALL( 'Table' ). That currently gets rid of any filters, but you will need to rewrite it to selectively keep the filters you want to apply, while removing the filters coming from the visualisation itself.

One key change is that you do not want to return a value if it is less than the boundary - that should instead be grouped into "others".

[SebastianY · Accepted solution]

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