Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi,
Here's my suggestion
Hope this helps.
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
@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?
@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
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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?
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please find the one drive link
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |