Forum Discussion

abhishekrws01's avatar
3 years ago
Solved

Create 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...
  • Jihwan_Kim's avatar
    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
        )