topic Re: PowerPivot measures: calculate a "SUMIFS" function, connecting 3 tables and 2 different fields in DAX Commands and Tips

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

fedefabio — Tue, 22 Mar 2022 04:37:57 GMT

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

stock out and stock in by serial nr
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!!!

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

tamerj1 — Tue, 22 Mar 2022 04:57:54 GMT

Hi fedefabio

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

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

fedefabio — Tue, 22 Mar 2022 05:47:38 GMT

Hi tamerj1

sure 🙂

cheers!

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

tamerj1 — Tue, 22 Mar 2022 06:01:14 GMT

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!

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

fedefabio — Tue, 22 Mar 2022 06:22:47 GMT

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! 🙂

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

tamerj1 — Tue, 22 Mar 2022 09:36:34 GMT

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.

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

fedefabio — Tue, 22 Mar 2022 13:43:20 GMT

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

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

tamerj1 — Tue, 22 Mar 2022 14:22:17 GMT

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

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

fedefabio — Wed, 23 Mar 2022 03:00:59 GMT

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!

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

tamerj1 — Wed, 23 Mar 2022 05:23:53 GMT

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:

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.
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.
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.
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!

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

fedefabio — Fri, 16 Dec 2022 06:56:37 GMT

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! 🙂