Forum Discussion

abhishekrws01's avatar
abhishekrws01
Helper I
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. 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.

 

 

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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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!!! ❤️

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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
    )