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.
I have the following 2 tables and wanted help to have a chart done as below from the 2 tables in PBI Desktop.
The below chart I did in Excel, and wanted to get it done in PBI. I make a Pivot Table from Table-2 and then added another column and did a VLookup to pull the Capacity value from Table-1 and then did a Chart.
Another way we can do this in Excel is to add a new column to Table-1 and get the Sum of the Consumed Value for each Work Id from Table-2 (SUM based on Filter) and do a chart from Table-1.
Table-1
Program | WorkId | Capacity |
P1 | W1 | 25 |
P1 | W2 | 20 |
P1 | W3 | 28 |
Table-2
JobId | WorkId | Consumed |
101 | W1 | 10 |
102 | W1 | 4 |
103 | W1 | 8 |
104 | W2 | 12 |
105 | W2 | 12 |
106 | W3 | 15 |
107 | W3 | 10 |
Thanks,
Prabhat
Solved! Go to Solution.
@prabhatnath , create a common table work id and join with both tables and use that in visual
workid= distinct(union(distinct(Table1[workid]),distinct(Table2[workid])))
Sum of consumed and capacity with common dimension in visual will do
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...
Hi @prabhatnath ,
Please try below steps:
1. create a new table with below dax formula
Table3 = DISTINCT(UNION(VALUES(Table1[WorkId]),VALUES(Table2[WorkId])))
2. create two measure with below dax formula
Measure =
VAR _id =
SELECTEDVALUE ( Table3[WorkId] )
VAR tmp1 =
FILTER ( ALL ( Table1 ), [WorkId] = _id )
RETURN
SUMX ( tmp1, [Capacity] )
Measure2 =
VAR _id =
SELECTEDVALUE ( Table3[WorkId] )
VAR tmp1 =
FILTER ( ALL ( Table2 ), [WorkId] = _id )
RETURN
SUMX ( tmp1, [Consumed] )
3. add a clustered column chart with Table3 filed and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@amitchandak and @v-binbinyu-msft thanks for the reply on this and the reply helped me. I can select just one as a Solution I believe.
Hi @prabhatnath ,
Please try below steps:
1. create a new table with below dax formula
Table3 = DISTINCT(UNION(VALUES(Table1[WorkId]),VALUES(Table2[WorkId])))
2. create two measure with below dax formula
Measure =
VAR _id =
SELECTEDVALUE ( Table3[WorkId] )
VAR tmp1 =
FILTER ( ALL ( Table1 ), [WorkId] = _id )
RETURN
SUMX ( tmp1, [Capacity] )
Measure2 =
VAR _id =
SELECTEDVALUE ( Table3[WorkId] )
VAR tmp1 =
FILTER ( ALL ( Table2 ), [WorkId] = _id )
RETURN
SUMX ( tmp1, [Consumed] )
3. add a clustered column chart with Table3 filed and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@prabhatnath , create a common table work id and join with both tables and use that in visual
workid= distinct(union(distinct(Table1[workid]),distinct(Table2[workid])))
Sum of consumed and capacity with common dimension in visual will do
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...
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 |
---|---|
109 | |
108 | |
88 | |
75 | |
66 |
User | Count |
---|---|
125 | |
114 | |
98 | |
81 | |
73 |