Dynamic Running Total

[Anonymous]

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. 

[v-xulin-mstf]

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]

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]

Hi @Anonymous

Is your issue solved?

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

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.