Forum Discussion
fabiojoa
Helper I
Helper ICumulative Formula not Working
Hello DaxLovers friends!
I need to make a cumulative sum and I am using a ranking to assist in this task. I need to show in a table the Product Reference, Product Description, Sales Value,% of Total, Ranking and I need to put the Accumulated in order to create the ABCD Chart.
First, we create total sales for the period:
MTD-VPR-Vendas (Ano) =
CALCULATE(
SUMX(FAT_VENDAPRODUTO; FAT_VENDAPRODUTO[VPR_VALOR]);
FILTER('DIM_TEMPO'; 'DIM_TEMPO'[Date] >= [MTD-FIL-#varMinDateAno]
&& 'DIM_TEMPO'[Date] <= [MTD-FIL-#varMaxDateAno]
))
Then I ranked ABCD and it seems to work well!
ABC_RANK =
If ( [MTD-VPR-Vendas (Ano)]>0;
CALCULATE(
RANKX(ALL(DIM_PRODUTO[PRD_REFERENCIA];DIM_PRODUTO[PRD_DESCRICAO]);[MTD-VPR-Vendas (Ano)];;DESC;Skip);
ALLSELECTED(DIM_LOJA_META);ALLSELECTED(DIM_FIL_TEMPO)
))
But, my accumulated total doesn't work at all. I tried two different formulas and said I was frustrated
that I couldn't do something that I thought would be easier.
ABC_VENDAS_ACUMULADAS =
Var RankAtual = [ABC_RANK]
Return
If(
[MTD-VPR-Vendas (Ano)]>0;
Calculate(
[MTD-VPR-Vendas (Ano)];
Filter(
ALL(DIM_PRODUTO[PRD_REFERENCIA]); [ABC_Rank]<=RankAtual
)
)
)
Var RankAtual = [ABC_RANK]
Return
If(
[MTD-VPR-Vendas (Ano)]>0;
Calculate(
[MTD-VPR-Vendas (Ano)];
Filter(
ALL(DIM_PRODUTO[PRD_REFERENCIA]); [ABC_Rank]<=RankAtual
)
)
)
Could someone help me with this challenge that is driving me crazy?
No RepliesBe the first to reply