Modeling Help

[Anonymous]

Hi,

I need help with modeling my data model to give certain results.

So the tables I have are:

• Item Master Table
• Sales Data
• Product Recipe Data

Item Master Table

Item Master

A

B

C

1

2

3

Sales Data

Item	Sales	Base Currency
A	100	GBP
A	200	EUR
B	300	GBP
B	400	EUR
C	500	GBP
C	600	EUR

Product Recipe Data

Parent Item	Recipe Item
A	1
A	2
A	3
B	2
B	3
C	1
C	2

My Model currently:

What I want to do:

When I search for a Recipe Item Number, I want a list of all Parent Items associated with it and then YTD Sales of the corresponding Parent Item.

Example:
When you search "2", you should get a list of {"A", "B", "C"}.

Please help!

Thanks & Regards.

[Anonymous]

Hi,

I checked the file you shared. My Item Master table is not the way you've created it.

Rather, it is this way, which changes how the filtering works.

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Measure:

Sales YTD = 
CALCULATE ( SUM ( 'Sales Data'[Sales] ),
    FILTER ( 'Sales Data',
        [Item] = SELECTEDVALUE ( 'Product Recipe Data'[Parent Item] )
    )
)

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.