Numeric Distribution Calculation

[paweldm]

Dear Community Members!

I need your help to solve the following scenario.

I have an input table with the set of columns.

Table Name: Distribution

Table Columns:

StoreID | Delivery Date |  DistributorID | ProductID | Quantity |

S100 | 2017-01-01 | X200 | PR100 | 150

S101 | 2017-01-01 | X120 | PR 101 | 200 

S102 | 2017-01-02 | X125 | PR 100 | 160

I need to calculate the followig measures:

1. Total count of all selected stores 
2. Total count of active stores within all selected stores
3. Numeric distribution as a % of active stores to all selected stores 
4. Running total of numeric distribution across time 
5. Delivery date when running total of numeric distribution takes specific value for example 10% or 20 % or any other value

The business enquiry is to answer the question for optional selection of filters on distributor(-s) , product(-s), store(-s), delivery date(-s) to indicate the specific date when measure #4 takes a specified value. 

I am attaching the model I have done to solve the scenario. It is only partly correct and I believe it is not optimal since I created additional table and new calculated columns. 

I am aware it is complex scenario but I really count on PowerBI community to help me.

Thx in advance. 

Paweł 

[v-chuncz-msft]

@paweldm,

It would be better to break down the problem into smaller parts. You may raise multiple threads to make the question more specific.

[paweldm]

@v-chuncz-msft

Thank you for your suggestion. I added the new thread here: https://community.powerbi.com/t5/Desktop/Numeric-Distribution-Calculation-thread-1/m-p/306341/highli...

I hope it makes more clear what I need to calculate. 

Pawel

[v-chuncz-msft]

@paweldm,

Always remember to simplify the sample as much as possible.

[paweldm]

https://drive.google.com/open?id=1uQ1JiyTjndy54qqTc_Xi0wtL58ssew8v