Forum Discussion

Karthik12's avatar
Karthik12
Post Patron
3 years ago

Table Visualization from 2 different tables

Hello Folks,

GM. 

I am developing a production data display whereas data comes from 2 different tables.

I would like consolidate in one matrix/table visualization as below

 

Expected format (Filer applied as today) :

 

Input files : Table 1 

Table 2 :

 

Can anybody help me, how to do this?

 

Thanks a lot in advance.

Need Help
Show and Tell

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Karthik12 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Production Qty =
COUNTX(FILTER(
    ALL('Table1'),
    CONTAINSSTRING(
    CONCATENATEX(
    'Table1','Table1'[Type],","),'Table'[Type])=TRUE()
    &&
    'Table1'[Type]=EARLIER('Table'[Type])
    ),[Type])
    Plan Qty =
COUNTX(FILTER(
    ALL('Table2'),
    CONTAINSSTRING(      
    CONCATENATEX(
    'Table2','Table2'[Type],","),
    'Table'[Type]  
    )=TRUE()
    &&
    'Table2'[Customer]=EARLIER('Table'[Type])
    ),'Table2'[Type])
    Gap =
[Production Qty] - [Plan Qty]

    2. Create measure.

    Flag =
IF(
    MAX('Table'[Gap] ) <0,"red","green")

    3. [Gap] – Conditional formatting – Background color.

    4. Result:

    When your entire Row is null, it will automatically default not to display.

    If you want to display null values, you can click Show items with no data.

     

    Best Regards,

    Liu Yang

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

    • Karthik12's avatar
      Karthik12
      Post Patron
      3 years ago

      Anonymous Hi , Thanks for your time.

      It working fine but we have plan qty and prod qty is more than 1 (in table 1 and table 2) means this logic not working.

      Is there any solution for that?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    One of ways to solve this is to create a Type dimension table like below.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

     

     

    • Karthik12's avatar
      Karthik12
      Post Patron
      3 years ago

      Jihwan_Kim I working for me. Thank you !🙂

      But only thing, If Gap color is not working. If Gap > 0, it should be green, <0, it should RED.

      Can you please check?

       

      Also, my table has lot of other names like below

      Is it possible to show only rows which has either plan or prod qty numbers?

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Thank you for your feedback.

        Please share your sample pbix file's link (onedrive or others), and then I can try to look into it to come up with a more accurate solution.

        Thanks.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Here's my suggestion

    1. Rename the Qty columns to Quantity in both tables
    2. Insert Type column with entries as Production and Plan in each table
    3. Append the tables
    4. Write these measures
      1. Pl_qty = sum(Data[Plan])
      2. Pr_qty = sum(Data[Production])
      3. Gap = [Pr_qty]-[Pl_qty]

    Hope this helps.