Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic Running Total

Hi all,

 

This is my first post in this community and I have a problem that has been troubing me for some time now and I am curious, at this point, if it is even possible.

 

So, I have transactional data of ordered quantity by customers. My goal is to create a running total for this ordered quantity starting from the earliest order date to the latest, by material (SKU) number. So each SKU should have its own running total. 

 

This first part is simple, but where I get stuck is that I want to incorporate a measure to select which customer to start with on all dates. 

 

For example if I only have 10 pieces of a SKU in stock and 5 customers each ordering 3 on a date, I want to be able to select (with a slicer) which customer(s) will get the pieces we have in stock. 

 

I cannot seem to find a way to incorporate column level filters with a measure as a rank without it either giving me an error or returning the wrong totals. Hopefully this all makes sense and if not I would be happy to clear it up in the comments.

 

Thanks in advance for all of your help. 

Need Help

4 Replies

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Anonymous,

     

    If I understand correctly, what you need is to calculate the total among the customers selected by the slicer.

    You can use ISFILTERED function to meet this.

    You can try measure like:

    IF(
    ISFILTERED([customers]),
    [Measure],
    BLANK()
)

     

    Best Regards,
    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      You are not too far off from what I am trying to accomplish. Rather than a filter though, I want it to be more like a rank. So if a customer is selected, all other customers still show in the visual and you can watch their inventory drop because it is being allocated to the selected customer.

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        Hi Anonymous,

         

        What is the calculation logic of  rank?

        Could you provide your expected output in format of excel?

         

        Best Regards,
        Link

         

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.