Forum Discussion
Running Total by Currency
- 4 years ago
The reason that you are seeing repeating Cumulative sales amount is because the measure relies on the rank of the sales amount. If the sales amount per order is the same, then you get the same rank,
There is a hack to solve this (in a random sort of way, since you need to break the rank for equal sales values. The way to do this is:
Create a new column in the table which adds a minute amount to each sales value per order, such as :
You can now use this column to establish the rank by order number, as in:
Rank (Random) Sales = IF ( NOT ( ISBLANK ( [Sum Random sales] ) ), RANKX ( ALL ( 'Summary Table'[SalesOrderNumber] ), [Sum Random sales], , DESC, DENSE ) )Now that you have a new rank by order number, you can calculate the cumulative for the original sales amount based on this rank using:
Running total Sales Amount (random) = VAR RNK = [Rank (Random) Sales] RETURN IF ( ISINSCOPE ( 'Summary Table'[SalesOrderNumber] ), CALCULATE ( [Summary Sales], FILTER ( ALLSELECTED ( 'Summary Table'[SalesOrderNumber] ), [Rank (Random) Sales] <= RNK ) ) )And you will get
Attached is the new sample file
Thank you very much for your help Paul. I need time to understand the differents steps you went throw..However when I look at the results in the print-screen, it seems that the sales amount are not cumulating but repeating...I was expecting to see that for each currency change, the Sales AMount starts from 0 then summing up when running throw all the sales orders contained in the currency.
Thanks again.
The reason that you are seeing repeating Cumulative sales amount is because the measure relies on the rank of the sales amount. If the sales amount per order is the same, then you get the same rank,
There is a hack to solve this (in a random sort of way, since you need to break the rank for equal sales values. The way to do this is:
Create a new column in the table which adds a minute amount to each sales value per order, such as :
You can now use this column to establish the rank by order number, as in:
Rank (Random) Sales =
IF (
NOT ( ISBLANK ( [Sum Random sales] ) ),
RANKX (
ALL ( 'Summary Table'[SalesOrderNumber] ),
[Sum Random sales],
,
DESC,
DENSE
)
)
Now that you have a new rank by order number, you can calculate the cumulative for the original sales amount based on this rank using:
Running total Sales Amount (random) =
VAR RNK = [Rank (Random) Sales]
RETURN
IF (
ISINSCOPE ( 'Summary Table'[SalesOrderNumber] ),
CALCULATE (
[Summary Sales],
FILTER (
ALLSELECTED ( 'Summary Table'[SalesOrderNumber] ),
[Rank (Random) Sales] <= RNK
)
)
)
And you will get
Attached is the new sample file
- Sylvain744 years ago
Helper III
Hello Paul,
First of all, sorry for my late answer, I had quite a heavy workload end of last week and did not have time so far to revert.
Your explanation is quite clear! I will study your 2nd pbix file to understand it in details.
Thanks again.