How to link "Bill of Material - Component - Item" with the "Sales of Item"?

[EZV12]

Hello,

I need help to figure out how to link the table of "Bill of Material" with the one of "Sales".  For example, when I choose a component "Compo 1"  in the table of "Bill of Material", it shows all items using this component.  In the table of "Sales", I need to see at the same time the sales of all items using "Compo 1".  

Please find here attached 4 images for more explications/questions as well as the link to pbix file https://1drv.ms/u/s!AnbNXrfr-X8jambSUMpSe6vYviE?e=2PyaIC  .

Many thanks in advance for your kind help.

[Anonymous · Accepted solution]

Hi @EZV12 ，

I updated your sample pbix file, please check whether that is what you want. You can find the details in the attachment.

1. Create two measures as below to get Sales qty and Sales €

Sales Qty = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Bill of Material'[Item] ),
        FILTER (
            'Bill of Material',
            'Bill of Material'[Component] = SELECTEDVALUE ( 'Bill of Material'[Component] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Quantity] ),
        FILTER ( 'Sales', 'Sales'[Item] IN _tab )
    )

Sales € = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Bill of Material'[Item] ),
        FILTER (
            'Bill of Material',
            'Bill of Material'[Component] = SELECTEDVALUE ( 'Bill of Material'[Component] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Revenue] ),
        FILTER ( 'Sales', 'Sales'[Item] IN _tab )
    )

2. Sort by month name base on Month field

Best Regards

[Anonymous · Accepted solution]

Hi @EZV12 ，

I updated your sample pbix file, please check whether that is what you want. You can find the details in the attachment.

1. Create two measures as below to get Sales qty and Sales €

Sales Qty = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Bill of Material'[Item] ),
        FILTER (
            'Bill of Material',
            'Bill of Material'[Component] = SELECTEDVALUE ( 'Bill of Material'[Component] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Quantity] ),
        FILTER ( 'Sales', 'Sales'[Item] IN _tab )
    )

Sales € = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Bill of Material'[Item] ),
        FILTER (
            'Bill of Material',
            'Bill of Material'[Component] = SELECTEDVALUE ( 'Bill of Material'[Component] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Revenue] ),
        FILTER ( 'Sales', 'Sales'[Item] IN _tab )
    )

2. Sort by month name base on Month field

Best Regards

[EZV12]

Dear Yingyinr,

I hope you are doing well. For the same pbix report, I have other questions.  Please see below printscreen and also the link Onedrive to the file :

https://1drv.ms/u/s!AnbNXrfr-X8jb0xcr7qqimwi3pA?e=qtrrp9 

Many thanks in advance for your kind help!

Best regards

Rachel

[Anonymous · Accepted solution]

Hi @EZV12 ，

It involves multiple slicer selecitons, so SELECTEDVALUE function will not be proper... Please update the formula of these measures (Sales Qty, Sales €,Budget Qty, Budget €, FO Qty, FO €) with the codes "IN  ALLSELECTED( 'Bill of Material'[Component] )" to instead of "=SELECTEDVALUE( 'Bill of Material'[Component] )" just as shown in below screenshot. You can find the details in the attachment.

Sales € =

VAR _tab =

CALCULATETABLE (

VALUES ( 'Bill of Material'[Item] ),

FILTER (

'Bill of Material',

'Bill of Material'[Component] IN  ALLSELECTED( 'Bill of Material'[Component] )

)

)

RETURN

CALCULATE (

SUM ( 'Sales'[Revenue] ),

FILTER ( 'Sales', 'Sales'[Item] IN _tab )

)

Best Regards

[EZV12]

Dear Yingyinr,

You are my savior!! Thanks a lot!

Best regards

Rachel

[EZV12]

Dear yingyinr,

It works!! Thank you very much for your great help!!! I'm so happy!

Take care and best regards. (I may ask you other questions later! 🙂 )

[StefanoGrimaldi]

sure if you send the PBIx its way easier to see the problem, thats a good idea from the start always if you can send it with dummy data so people can check and respond quickier for your problem 

[EZV12]

Hello again, here is the link for the pbix file. May you please check and help? Many thanks.

https://1drv.ms/u/s!AnbNXrfr-X8jambSUMpSe6vYviE?e=2PyaIC 

[StefanoGrimaldi]

hey,

1) change the direction of the tables conected to the calendar that have 2 way connection, make sure in the relationship setting calendar its the primary table and direction its 1 direction (single) no both direction, (this will avoid circular dependecy between relantionship and cases that cant be linked because of it)

2) same for the item table (billl of material) take the same considreation in this case as amany to many set the direction bill of material to filter the other table

3) consider creating a master material table as dimensional table and conecting the bill of material to it and them conect that one to facts tables to avoid use the many to many unless totally necesary and have a better star schema since Power BI love star schemas 🙂 

if this was helpfull give some kudos and mark as solution for others to find

[EZV12]

Thank you for your reply, however, it doesn't work. Or maybe I did not do it correctly.  Is it possible to send you the pbix file? It's a simple example and the file is not heavy.  But there is no way to attach it here.

thanks in advance.