Forum Discussion

Jo_Chrq's avatar
Jo_Chrq
Icon for Helper I rankHelper I
3 years ago
Solved

Distribution of a variable in matrix

Hi all,

Once again I need your precious help 🙂

 

I need to find the measure that helps me create the followinfg column:

 

 

Data model is like this:

  1. Store Table, linked with
  2. Stock Table, and
  3. Sales Table

 

In a matrix I have a first column with the quantity in stock per store and in a specific warehouse (all in store table)

Second column is the distribution of the sales per stores (Warehouse is not concerned)

Third (which I struggle with) is the amount in stock per store + the warehouse stock quantity, distributed in each store based on the sales ratio above

 

Many thanks in advance for your help,

Best

Jonathan

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Jo_Chrq 

     

    You can try the following methods.

    Sample data:

    Store Table:

    Sales Table:

    Measure:

    Stock+Warehouse distrib = 
Var _N1=SUM('Store Table'[Stock])
Var _N2=SUM('Sales Table'[Sales distrib])
Var _warehouse=CALCULATE(SUM('Store Table'[Stock]),FILTER(ALL('Store Table'),[Store]="Warehouse"))
Return
IF(_N2=BLANK(),BLANK(),_N1+_N2*_warehouse)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Jo_Chrq 

     

    You can try the following methods.

    Sample data:

    Store Table:

    Sales Table:

    Measure:

    Stock+Warehouse distrib = 
Var _N1=SUM('Store Table'[Stock])
Var _N2=SUM('Sales Table'[Sales distrib])
Var _warehouse=CALCULATE(SUM('Store Table'[Stock]),FILTER(ALL('Store Table'),[Store]="Warehouse"))
Return
IF(_N2=BLANK(),BLANK(),_N1+_N2*_warehouse)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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