Forum Discussion

pdory's avatar
pdory
Frequent Visitor
2 years ago
Solved

Need help with cumulative total without date

Hi everyone,

 

I'm looking for some help to create a DAX measure that will show the cumulative total of the Supplier Spend starting from the highest value. 

Here is a link to the pbix: Link to sample data 

Requirements:

  1. Create a table where I can see the Supplier Name, the total Spend for the Supplier, the Ranking (highest to lowest spend), the running total (starting from highest), the running total percentage.
  2. The table can be sliced by the Year column and give accurate results
  3. The solution should only use DAX or existing columns. No calculated columns or calculated tables should be added to the model.  



One of the roadblock seems to be the presence of duplicate rows for Supplier Name in the Spend table. 

Another roadblock was the use of dynamic ranking instead of hardcoding an index (which I don't want to do).
So far all attempts to acheive this on my end provided unexpected results or severe performance issues.  

Any help would be greatly appreciated. Thanks!

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    pdory 

     

    output : 

     

     

    measure : 

    Measure = 
CALCULATE(
    [spend],
    WINDOW(
        0,ABS,
        0,REL,
        ALLSELECTED(Spend[Supplier Name]),
        ORDERBY([spend] ,DESC)
    )
)

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

  • Daniel29195's avatar
    Daniel29195
    2 years ago

    pdory 

    forgot to mention : 

    create a measure 

    spend = sum(table_name[spend]) 

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

  • Daniel29195's avatar
    Daniel29195
    2 years ago

    pdory 

    try this one : 

    spend = SUM(Spend[Supplier Spend])

     
    rnk =
     RANKX(ALLSELECTED('Spend'[Supplier Name]), [Spend], , DESC, Dense)
     
     
    Running Total =
    VAR CurrentRank = [rnk]
    RETURN
    CALCULATE(
        [spend],
        FILTER(
            ALLSELECTED(Spend[Supplier Name]),
            [rnk] <= CurrentRank
        )
    )
     
     
     
    let me know if this works for you .
     
     
     
     

8 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    pdory 

     

    output : 

     

     

    measure : 

    Measure = 
CALCULATE(
    [spend],
    WINDOW(
        0,ABS,
        0,REL,
        ALLSELECTED(Spend[Supplier Name]),
        ORDERBY([spend] ,DESC)
    )
)

     

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

    • pdory's avatar
      pdory
      Frequent Visitor
      2 years ago

      Thanks for the reply. Not sure what I did wrong but I'm getting this error. It does not let me refer to the Spend table.

       

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        pdory 

        forgot to mention : 

        create a measure 

        spend = sum(table_name[spend]) 

         

        If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
        It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    How do you define the  running total (starting from highest) ?

    Otherwise, I assumed the following :

    Total Spend = SUM('Spend'[Supplier Spend])
Spend Rank = RANKX(ALL('Spend'[Supplier Name]), [Total Spend], , DESC, Dense)

Running Total = 
VAR CurrentRank = [Spend Rank]
RETURN
CALCULATE(
    [Total Spend],
    FILTER(
        ALLSELECTED('Spend'),
        [Spend Rank] <= CurrentRank
    )
)

Running Total Percentage = DIVIDE([Running Total], CALCULATE(SUM('Spend'[Supplier Spend]), ALLSELECTED('Spend')))