How to get Direct Values in Column Chart instead of Aggregated values

[prabhatnath]

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

[amitchandak · Accepted 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...

[prabhatnath]

@amitchandak and @Anonymous thanks for the reply on this and the reply helped me. I can select just one as a Solution I believe.  

[Anonymous · Accepted solution]

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 · Accepted 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...