Suma Acumulada de Venta ( Mayor a Menor de Acuerdo a la Venta )

[jpaz]

Hola Tengo una consulta sobre la Suma Acumulada, hay medida Rápidas que acumulan en Base a Fecha u orden alfabético. Tengo una Tabla con Proveedores y Ventas, lo que busco es una DAX que  acumule las Ventas tomando como criterio el que tiene Mayor Venta de Mayor a Menor.

Proveedor	Venta
A	150
B	200
C	50
D	300

Lo que necesito es Ordenar por Venta y que se acumule en Base a la Mayor Venta el Resultado deberia ser:

Proveedor	Venta	Acumulado	<=funcion DAX
D	300	300
B	200	500
A	150	650
C	50	700
TOTAL	700	700

Tienen Alguna idea?

Es decir mi criterio de Ordenación no es la Fecha sino -El que tiene más Venta-.

Agradezco su ayuda.

Saludos

William

[v-luwang-msft · Accepted solution]

Hi @jpaz ,

you could try the following steps:

Step 1, use rankx to sort base data,create a column use the following dax:

RANK = RANKX(ALL('Table'),'Table'[Venta],,desc)

Step 2,use the following measure to  calculate sum:

final = CALCULATE(SUM('Table'[Venta]),FILTER(ALL('Table'),'Table'[RANK]<=MAX('Table'[RANK])))

 

Wish it is helpful for you!

Best Regards

Lucien 

[v-luwang-msft · Accepted solution]

Hi @jpaz ,

you could try the following steps:

Step 1, use rankx to sort base data,create a column use the following dax:

RANK = RANKX(ALL('Table'),'Table'[Venta],,desc)

Step 2,use the following measure to  calculate sum:

final = CALCULATE(SUM('Table'[Venta]),FILTER(ALL('Table'),'Table'[RANK]<=MAX('Table'[RANK])))

 

Wish it is helpful for you!

Best Regards

Lucien 

[jpaz]

Thanks Lucien!
This is fine when there is little data, but I have a 5 year history and would like to be able to filter by year-month to see the accumulated by provider in each case. What could I do?

[v-luwang-msft · Accepted solution]

Hi  @jpaz ,

Use the following dax,You can sort by month group and group for summary:

rank=RANKX(FILTER('Table','Table'[month]=EARLIER('Table'[month])),'Table'[Venta],,desc,Dense)

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak]

@jpaz , Venta is column

calculate(sumx(filter(values(Table[Proveedor]), table[Venta] <=max(Table[Venta])),Table[Venta]), allselected(Table))

if Venta is a measure

calculate(sumx(filter(values(Table[Proveedor]), [Venta] <=maxX(Table,[Venta])),[Venta]), allselected(Table))

[jpaz]

Hi, thanks for your answer

Gracias por tu respuesta

I did a test with the proposed solution, But the result is that the total sum is repeated in each Row.

This is my measure: 

Acum = calculate(sumx(filter(values(Tabla[Proveedor]), [Venta_1] <=maxX(Tabla,[Venta_1])),[Venta_1]), allselected(Tabla))

And this is the result

Proveedor     Venta_1     Acum
FAMCTEX       11,405       77,020
CRUZ H            7,534      77,020
AGUILAR          6,076      77,020
HUA HOK         5,223     77,020
SUMAR BINDA  4,519   77,020
MARIN PEREZ   3,090   77,020
. . .
. . .
. . .
. . .
TOTAL           77,020     77,020

What I can do?