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.
Hi Everyone
I have 2 tables.
Table 1 contains emlpoyees' actual performance and;
Table 2 contains employees' target perfprmance
Table 1 looks like as follow
Date Name Product Qty Sold Sales Amount
1/1/2021 Mr A B 2 1,000
2/1/2021 Mr A A 3 1,000
3/1/2021 Mr A C 1 1,000
Table 2 looks similar to table 2 in columns, buts its a targt data
Date Name Product Qty Sold Sales Amount
1/1/2021 Mr A B 1 1,000
2/1/2021 Mr A A 4 1,333
3/1/2021 Mr A C 2 2,000
I want the output as follow
Date Name Product Qty Sold-Actual Sales Amount-Actual Qty Sold-Target Sales Amount-Target
1/1/2021 Mr A B 2 1,000
2/1/2021 Mr A A 3 1,000
3/1/2021 Mr A C 1 1,000
1/1/2021 Mr A B 1 1,000
2/1/2021 Mr A A 4 1,333
3/1/2021 Mr A C 2 2,000
Summary want to append the first 3 columns (dimensions) to the existing columns but wants to add additional columns for the taget meassures(Qty sold and sales amount).
Thank you so much for all your help.
Solved! Go to Solution.
There are at least two good ways you could do this.
1. Merge your second table into the first using the first three columns, and then write measures like this
Qty Sold - Actual = SUMX(Table, Table[Qty Sold] - Table[Qty Target])
2. Add a custom column called "Type" (or whatever you want) with text values of "Sold" and "Target" in the two tables, and then append them (load only the appended table) and then write measures like
Qty Sold - Actual =
var soldqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Sold")
var targetqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Target")
return soldqty - targetqty
Note in both cases, it is recommended to use measures instead of creating calculated columns.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@rashidanwar
So the following steps in Power Query:
1. Duplicate the Sales Amount Column
2. Rename the Qty Sold Sales Amount to something line Qty Sold-Target and Sales Amount Target
3. Select Table 1 and choose append as a new query
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@rashidanwar
So the following steps in Power Query:
1. Duplicate the Sales Amount Column
2. Rename the Qty Sold Sales Amount to something line Qty Sold-Target and Sales Amount Target
3. Select Table 1 and choose append as a new query
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
There are at least two good ways you could do this.
1. Merge your second table into the first using the first three columns, and then write measures like this
Qty Sold - Actual = SUMX(Table, Table[Qty Sold] - Table[Qty Target])
2. Add a custom column called "Type" (or whatever you want) with text values of "Sold" and "Target" in the two tables, and then append them (load only the appended table) and then write measures like
Qty Sold - Actual =
var soldqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Sold")
var targetqty = CALCULATE(SUM(Table[Qty]), Table[Type] = "Target")
return soldqty - targetqty
Note in both cases, it is recommended to use measures instead of creating calculated columns.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@rashidanwar , rename the last two columns in the second table. Append in power query and add index column. Use the same index column in visual (not summarized)
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |