Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
)
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 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
)
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.
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.