Forum Discussion
abhishekrws01
Helper I
3 years agoCreate a table through DAX formula by combining two tables unique record
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...
- 3 years ago
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 )
Jihwan_Kim
Super User
3 years agoHi,
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
)
- abhishekrws013 years ago
Helper I
Thank you very much for your help!