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 s...
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 .
     
     
     
     
Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

pdory 

 

if it works for you , kindly  mark it as a an accepted solution ✅.
It  might help someone else to find the solution more easily. Thanks for spreading the good vibes! 🤠

pdory's avatar
pdory
Frequent Visitor
2 years ago

Thanks Daniel, it has been accepted as the solution. I do have a followup question - when I use this measure in the same table but with 1000s of Supplier names, the table visual breaks and says it exceeded the available resources. I tried using a visual level filter for only the top 10 ranks but it still exceeded resources.

 

Is there any way I can improve the performance of this measure?

Would adding a similar filter directly in the measure be more beneficial vs visual level filter?

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    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 .
     
     
     
     
    • pdory's avatar
      pdory
      Frequent Visitor
      2 years ago

      I still got the error a few times but it eventually worked. Thank you for your help!