Forum Discussion
Sylvain74
Helper III
4 years agoRunning Total by Currency
Dears, I am currently training getting more familiar with Dax in Power BI, and I try to do a running total by currency. To do so, I am using the AdventureWorksDW2017 database and then FactIntern...
- 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
Sylvain74
Helper III
4 years agoHello,
Any other suggestions or ideas to help me?