Forum Discussion

biengineer's avatar
biengineer
Helper I
3 years ago
Solved

Summarize Multiple Values from Different Table within either of Table - File Attached

Dear All,

 

Hope you all are doing well.

 

I wish to Summarize based on different columns from different tables with Many - Many Relationship.

 

Download pbix:

https://we.tl/t-XAMbWL3KEo  

 

 

 

Best Regards.

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi biengineer ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a table.

     

    Table =
GENERATE (
    SUMMARIZE ( 'A', 'A'[Machine Name], 'A'[Operation], 'A'[Time] ),
    SELECTCOLUMNS (
        RELATEDTABLE ( 'B' ),
        "Item", 'B'[Item],
        "Mcss", 'B'[Mcss],
        "Quantity", 'B'[Quantity]
    )
)

     

    (3) Then the result is as follows.

     

    Please refer to the following document for more information.

     Summarizing values from different tables - Microsoft Power BI Community

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi biengineer ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a table.

     

    Table =
GENERATE (
    SUMMARIZE ( 'A', 'A'[Machine Name], 'A'[Operation], 'A'[Time] ),
    SELECTCOLUMNS (
        RELATEDTABLE ( 'B' ),
        "Item", 'B'[Item],
        "Mcss", 'B'[Mcss],
        "Quantity", 'B'[Quantity]
    )
)

     

    (3) Then the result is as follows.

     

    Please refer to the following document for more information.

     Summarizing values from different tables - Microsoft Power BI Community

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous , 

      In your answer, is it possible to insert excluding filters?

      I mean, using your answer as an example, is it possible to exclude when the column "item" from related "b" is different than null, or "J", etc...

      Example:

      Table =
GENERATE (
    SUMMARIZE ( 'A', 'A'[Machine Name], 'A'[Operation], 'A'[Time] ),
    SELECTCOLUMNS (
        RELATEDTABLE ( 'B' ),
        "Item", IF('B'[Item] <> NULL),
        "Mcss", 'B'[Mcss],
        "Quantity",'B'[Quantity]
    )
)

       

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi biengineer ,

     

    What is the final result you want to achieve? Can you share how the calculation shoud be handle? Can you pledase give some more insights.

     

    Has a good start for the many to many check this video