Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have two tables, forecast & actual, where data is at the country & Product levels. I want to compare how I am doing against the forecast at an aggregate level and by country & product level. It is possible that for a few countries, I don't have a forecast but have actuals vv few countries have forecast but don't have actuals. I am trying to create a 3rd table where I can see all the countries, including ones that have no forecast, but actual vv have forecast but no actuals. The table looks like this.
Forecast table
Country Product Sales
Country 1 product 1 100
Country 2 product 2 200
Country 5 product1 100
Actual table
Country Product Sales
Country 1 product 1 120
Country 4 product 2 220
Country 3 product 1 200
Desired table
Country Product ForecastSales Actual Sales
Country 1 product 1 100 120
Country 2 product 2 200
Country 3 product 1 200
Country 4 product 2 220
Country 5 product1 100
Please help if possible
Thank you.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
New Table =
VAR _country =
DISTINCT (
UNION ( DISTINCT ( ForecastSales[Country] ), DISTINCT ( ActualSales[Country] ) )
)
VAR _product =
DISTINCT (
UNION ( DISTINCT ( ForecastSales[Product] ), DISTINCT ( ActualSales[Product] ) )
)
VAR _countryproduct =
SELECTCOLUMNS (
GENERATE ( _country, _product ),
"@country", [Country],
"@product", [Product]
)
RETURN
FILTER (
ADDCOLUMNS (
_countryproduct,
"@ForecastSales",
CALCULATE (
SUM ( ForecastSales[Sales] ),
FILTER (
ForecastSales,
ForecastSales[Country] = [@country]
&& ForecastSales[Product] = [@product]
)
),
"@ActualSales",
CALCULATE (
SUM ( ActualSales[Sales] ),
FILTER (
ActualSales,
ActualSales[Country] = [@country]
&& ActualSales[Product] = [@product]
)
)
),
[@ForecastSales] <> 0
|| [@ActualSales] <> 0
)
Hi,
Please check the below picture and the attached pbix file.
New Table =
VAR _country =
DISTINCT (
UNION ( DISTINCT ( ForecastSales[Country] ), DISTINCT ( ActualSales[Country] ) )
)
VAR _product =
DISTINCT (
UNION ( DISTINCT ( ForecastSales[Product] ), DISTINCT ( ActualSales[Product] ) )
)
VAR _countryproduct =
SELECTCOLUMNS (
GENERATE ( _country, _product ),
"@country", [Country],
"@product", [Product]
)
RETURN
FILTER (
ADDCOLUMNS (
_countryproduct,
"@ForecastSales",
CALCULATE (
SUM ( ForecastSales[Sales] ),
FILTER (
ForecastSales,
ForecastSales[Country] = [@country]
&& ForecastSales[Product] = [@product]
)
),
"@ActualSales",
CALCULATE (
SUM ( ActualSales[Sales] ),
FILTER (
ActualSales,
ActualSales[Country] = [@country]
&& ActualSales[Product] = [@product]
)
)
),
[@ForecastSales] <> 0
|| [@ActualSales] <> 0
)
Thank you very much for your help!
Hey my friend,
You can use a table with
merge =
DISTINCT(UNION(
SELECTCOLUMNS(Table_a, "Coluna1", [Expressão1], "Coluna2", [Expressão2], ...),
SELECTCOLUMNS(Table_b, "Coluna1", [Expressão1], "Coluna2", [Expressão2], ...)
))
If you need all lines, just remove the distinct part.
If it solved your problem, give me a kudo!!! ❤️
Thank you. It gave me the idea to think a bit differently.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |