Table Visualization from 2 different tables

[Karthik12]

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.

[Ashish_Mathur]

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.

[v-yangliu-msft]

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]

@v-yangliu-msft 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?

[AbbasG]

@Karthik12 multiple ways of doing it you can do it by relationship or merging the tables.

The below posts will guide you in the right direction.

https://community.powerbi.com/t5/Desktop/Joining-contents-of-two-tables-into-one-new-table/m-p/39775

https://curbal.com/curbal-learning-portal/joining-tables-in-power-bi-with-power-query-and-dax

[Jihwan_Kim]

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]

@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]

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.

[Karthik12]

@Jihwan_Kim 

Hi,

Please find the one drive link

https://vontier-my.sharepoint.com/:u:/g/personal/karthikeyan_palanisamy_gilbarco_com/EU-bwAe6V1hKl0t...