PowerPivot measures: calculate a "SUMIFS" function, connecting 3 tables and 2 different fields

[fedefabio]

Hello All,

I have this kind of problems.

for an Inventory management tool I am creating I have 3 different tables:

1. StockIN: all the entry records, main fields:

• Serial_nr_IN
• Product_name_IN
• WH_Categ_Lvl1_IN
  • WH_Categ_Lvl2_IN
• Qty_IN

2. StockOUT: all the out records

• Serial_nr_OUT
• Product_name_OUT
• WH_Categ_Lvl1_OUT
  • WH_Categ_Lvl2_OUT
• Qty_OUT

and then the table 'int_mov' that allows to change WH_cateogories, so related fields are:

• Serial_nr_mov
• Product_name_mov
• FROM_WH_Categ_Lvl1
  • FROM WH_Categ_Lvl2
• TO_WH_Categ_Lvl1
• TO WH_Categ_Lvl2
• Qty_mov

 *****

I created relationships between 

1. stock out and stock in by serial nr
2. int_mov and stock in by serial nr.

then I've created the power pivot table.

I am struggling with to create the calculated measure that basically allows me to calculate the actual net quantity per Wh_Category_lvl1(so far...). the concept is this one:

CURRENT QTY: qty_IN - Qty_OUT - Qty_mov[FROM] + Qty_mov[TO]

so I've created this:

=SUM(StockIN_entry[Quantity_Units_IN])

-

SUM(StockOUT_entry[Quantity_Units_OUT])

-

CALCULATE(SUM(internal_mov_entry[Quantity_Units_mov]),FILTER(internal_mov_entry,internal_mov_entry[FROM WH_Name_Lv1]=CALCULATE(VALUES(StockIN_entry[WH_Name_Lv1_IN]))))

+

CALCULATE(SUM(internal_mov_entry[Quantity_Units_mov]),FILTER(internal_mov_entry,internal_mov_entry[TO WH_Name_Lv1]=CALCULATE(VALUES(StockIN_entry[WH_Name_Lv1_IN]))))

but seems it cannot work properly with the last argument:

CALCULATE(SUM(internal_mov_entry[Quantity_Units_mov]),FILTER(internal_mov_entry,internal_mov_entry[TO WH_Name_Lv1]=CALCULATE(VALUES(StockIN_entry[WH_Name_Lv1_IN]))))

and finally, what if I want to add WH_cat_Lvl2?

thanks!!!

[tamerj1]

Hi @fedefabio 

how does your repor look like? Can you a screenshot?

[fedefabio]

Hi @tamerj1 

sure 🙂

cheers!

F

[tamerj1]

@fedefabio 
THe TESTNAME column in the visual from which table? Can you also share a sreeshot of your data model? Thak you and have a great day!

[fedefabio]

@tamerj1 from Stock_IN

sure!

this is StockIN_entry table

this is from Stock_out

and this below from int_mov

thanks to you and have a great day too! 🙂

[tamerj1]

Sorry @fedefabio for the late reply. I got distracted with some other stuff.

Actually I was asking about the data model (relationships). However from the sample data you've shared I can guess they're all one-one relationships. Yet, I cannot the see the reason behind manually filtering the sum from the 'int_mov' table. You can simply go for

Difference =
SUM ( StockIN_entry[Quantity_Units_IN] )
    - SUM ( StockOUT_entry[Quantity_Units_OUT] )
    - SUM ( internal_mov_entry[Quantity_Units_mov] )
    + SUM ( internal_mov_entry[Quantity_Units_mov] )

Unless the actual relationships are not as I expected them to be.

[fedefabio]

Hi @tamerj1 , no worries at all and thanks again for your time!

By the way, the formula you wrote was the first one I've tried but it was not working.

Relationships are many-to-one as below:

I am afraid that the issue stands in the 'int_mov' table where there are 2 different columns with "Wh_lvl1" (FROM and TO)

Cheers!
F

[tamerj1]

Excellent now all clear. Can you please write the names three columns involved in the relationships?

[fedefabio]

Hi @tamerj1 , sure!

in the pivot table we have

• row: StockIN_entry[Product_Name_IN]
• column(s): StockIN_entry[WH_Name_Lv1_IN]    (in future will add Lv2)

for calculation values, columns involves are:

• StockIN_entry[Quantity_Units_IN]
• StockOUT_entry[Quantity_Units_OUT]

and for internal movment table, we have:

• internal_mov_entry[Quantity_Units_mov]

with

• internal_mov_entry[FROM WH_Name_Lv1]
• internal_mov_entry[TO WH_Name_Lv1]

Hope everything is clear 🙂

cheers!

F

[tamerj1 · Accepted solution]

Hi @fedefabio 
Yes everything is clear now. I will try to explain the best I can. What you want to do can be achieved completely using DAX code but you will spend hard time trying to achieve that and the result code will be neither efficient nor readable. The reason is your report uses columns from the fact table and these columns cannot filter other tables automatically hence you need to write extra DAX code every step onwards. For example for the 'StockOUT' table you cannot just SUM the quantity column as it won't be filtered by the 'StockIN[Product_name_IN] cannot filter the 'StockOUT' table unless we CROSSFILTER the relationship to "Both". Example:

StockOUT Qty =
CALCULATE (
    SUM ( StockOUT_entry[Quantity_Units_OUT] ),
    CROSSFILTER ( StockIN_entry[Serial_nr_IN], StockOUT_entry[Serial_nr_OUT], BOTH )
)

But this will not solve the problem because you are slicing by two columns from the Stock-IN table while the relationship utilizes only one column. Here where things get complicated and it even gets more complicated when dealing with the 'int_mov'  table considering the two columns "From" and "To". And we're still talking about level 1 only!
That was the bad news. The good news is that there is a simple and efficient solution for that. As I don't have sample file to work with I would advise to do it in two steps. First step is fixing the data model and create proper relationships. 2nd step is writing DAX code which will be simple code. 
STEP 1:

1. Create the dimension tables: Since you are using power pivot, creating tables with DAX is not an option but you can easily create the required tables using either excel or power query.
   The first table is a single column table that contains all the unique "Serial Numbers'.
   The 2nd table is a single column table that contains all the unique level 1 HW names.
   The 3rd table is a single column table that contains all the unique  level 2 HW names.
2. Now you can start creating the relationship between tables the new tables and the existing ones:
   a. Serial number will connect to the three tables with active relationships.
   b. Level 1 will connect to the  three tables with active relationships. For the 'int_MOV' table the active relationship will be with the "From" column and one extra "Inactive" relationship with the "To" column.
   c. 
3. Level 1 will connect to the  three tables with active relationships. For the 'int_MOV' table the active relationship will be with the "From" column and one extra "Inactive" relationship with the "To" column.
4. Level 2 will connect to the  three tables with active relationships. For the 'int_MOV' table the active relationship will be with the "From" column and one extra "Inactive" relationship with the "To" column.

Once all relationships are set please share screenshots of the data model then will guide you through the next step.
Have a great day!

[fedefabio]

Hi @tamerj1 

first of all, apologies for my super late reply and really thanks so much for your help.

At the end I solved the issue building a bridge table and from it generate a pivot table.

thanks again! 🙂