Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
abhishekrws01
Helper I
Helper I

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.

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_0-1679204565182.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_0-1679204565182.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Thank you very much for your help!

Anonymous
Not applicable

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors