SUM sub items based on volume of a top item in the same table

[JeroenZuidlaren]

All, 

I have the following data model (simplified) which show production output (table 3) per top level item. Another table is showing the BOM (bill of material) structure, table 4. Both are linked to a central item table (table 2), production data is linked to a date table (table 1). In my report I use a date and item filter.

Final goal is to show for a selected period the usage of the different sub items. 

Example for Januari:

I imagine I need a measure performing the following steps:

1. Calculate total volume for Top level item in selected period for selected items (slicer)

2. Calculate total required qty per sub item, based on step 1 X Qty per

3. Measure to use for the final result (see above)

Anyone who could help with this?

thanks,

Jeroen

[v-zhangti]

Hi, @JeroenZuidlaren 

You can try the following methods.

Measure1 = CALCULATE(SUM('Table 3'[Volume]),FILTER(ALLEXCEPT('Table 3','Table 3'[Top level item]),[Date]=SELECTEDVALUE('Table 3'[Date])))

Measure2 = [Measure1]*SUM('Table 4'[Qty per])

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.

[JeroenZuidlaren]

HI @v-zhangti ,

Many thanks for this solution. It is almost perfect however I am struggling with dates. As Measure 1 is comparing a date it is looking for a specific day. Later on in the report selections will be done on Year, Month, Day etc. Below picture is the desired situation based on  your sample file. In this picture all production of top items took place at the same day (see Table 3). It shows:

1. Volume per top level item per month

2. Quantity of each sub item per top item

3. Combined volumes and sum of used sub items in selected period

4. Sum of used sub items in selected period [Final required situation]

In an updated sample file I have changed the date at line 2 in Table 3 to another day in January. As you will see the result is now different and the file is not including the 2nd day in January.

In my real file with production data there is production at almost every day and a lot of data is missing now.

Is there a way the measure 1 could be rebuilt to sum always per selected date period?

Thanks again. By the way: I don't know how to include to PBIX here.