Forum Discussion
Problem with TOPN
- 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' ) ) * PorcentajeElegidoVAR 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 - VentasTopNVAR EsOtroSeleccionado =SELECTEDVALUE ('Clientes con Otros'[Nombre]) = "Otros clientes"VAR resultado =IF (ISINSCOPE ( 'Clientes con Otros'[Nombre] ),IF (EsOtroSeleccionado,VentasOtro,IF([TotalVentas]>=PorcVentas,[TotalVentas])),[TotalVentas])Returnresultado
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".
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.