Forum Discussion

Sylvain74's avatar
Sylvain74
Icon for Helper III rankHelper III
4 years ago
Solved

Running 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 FactInternetSales, with a relationship  to DimCurrencyTable.

 

I created a matrix in which I group rows by CurrencyName and SalesOrderNumber and I display a measure called Sales Amount which derived from the column SaleAmount (Sales Amount = SUMX(FactInternetSales,FactInternetSales[SalesAmount]))

 

To calculate the running amount, I understood that I need a ranking therefore I created a SalesOrderIndex in FactInternetSales table and then created this measure:

 

Sales Amount RT by Currency =
VAR MaxSalesOrderIndex = MAX(FactInternetSales[SalesOrderIndex])
RETURN
CALCULATE([Count Sales Order Nb], FactInternetSales[SalesOrderIndex]<= MaxSalesOrderIndex)
 
Obviously, it does not work...
Can you please help me?
Thanks.
 
Best regards,
Sylvain
  • 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 

10 Replies

  • Sylvain74 , which table you have CurrencyName 

     

    You can try like


    Sales Amount RT by Currency =
    VAR MaxSalesOrderIndex = MAX(FactInternetSales[SalesOrderIndex])
    RETURN
    CALCULATE([Count Sales Order Nb], filter(FactInternetSales, FactInternetSales[SalesOrderIndex]<= MaxSalesOrderIndex && FactInternetSales[CurrencyName] = max(FactInternetSales[CurrencyName])))

    • Sylvain74's avatar
      Sylvain74
      Icon for Helper III rankHelper III

      Hi amitchandak ,

      I tried below dax statement where I replace CurrencyName by CurrencyKey since it does not exists in FactInternetSales. However it does not work...For each line, it gives the same amount thant the Sales Amount

      Sales Amount RT by Currency =
      VAR MaxSalesOrderIndex = MAX(FactInternetSales[SalesOrderIndex])
      RETURN
      CALCULATE([Sales Amount], FILTER(FactInternetSales, FactInternetSales[SalesOrderIndex]<= MaxSalesOrderIndex && FactInternetSales[CurrencyKey] = MAX(FactInternetSales[CurrencyKey])))
       
      By the way I would like to share the pbix file but I don't know how to. Where is the upload icon/button?
  • Dears,

     

    I am still stuck with this Running total... 😞 

    How can I join my pbix file, so that it will more convenient to help me?

    Thanks.

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion

      You can share a PBIX file using cloud services (OneDrive, Google Drive...)