Create a new table from the Main Table with criteria

[Power_It_Up]

Hi,

I currently have this example Main table. I'm only using client A (there are hundreds) and certain countries/items to try and explain this better:-

I need to create a new table which shows the missing Items based on the data.  So the new table should look like this:-

So for England, it has added Item C and a zero because the item exists for Client A, but only for USA.

For USA, it has added Item B and Item D with zero's as these exist for England.

This methodology has to run through many clients and items and each client will have different countries and items associated to them.

Thank you in advance for any pointers. 🙂

[pankajnamekar25 · Accepted solution]

You can use below DAX code to calculated table

ResultTable =

VAR ClientsCountries =

    SUMMARIZE(MainTable, MainTable[Client], MainTable[Country])

VAR Items =

    VALUES(MainTable[Item])

RETURN

    GENERATE(

        ClientsCountries,

        ADDCOLUMNS(

            Items,

            "AmountSold",

            CALCULATE(

                SUM(MainTable[AmountSold]),

                FILTER(

                    MainTable,

                    MainTable[Client] = EARLIER(MainTable[Client])

                        && MainTable[Country] = EARLIER(MainTable[Country])

                        && MainTable[Item] = EARLIER(MainTable[Item])

                )

            )

        )

    )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[pankajnamekar25 · Accepted solution]

You can use below DAX code to calculated table

ResultTable =

VAR ClientsCountries =

    SUMMARIZE(MainTable, MainTable[Client], MainTable[Country])

VAR Items =

    VALUES(MainTable[Item])

RETURN

    GENERATE(

        ClientsCountries,

        ADDCOLUMNS(

            Items,

            "AmountSold",

            CALCULATE(

                SUM(MainTable[AmountSold]),

                FILTER(

                    MainTable,

                    MainTable[Client] = EARLIER(MainTable[Client])

                        && MainTable[Country] = EARLIER(MainTable[Country])

                        && MainTable[Item] = EARLIER(MainTable[Item])

                )

            )

        )

    )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Power_It_Up]

Hi @pankajnamekar25 . 

I'm hoping you can direct me a little further with this as I need to add something.

This is the current code which you created for me and works brilliantly:-

ResultTable =

VAR ClientsCountries =

    SUMMARIZE(MainTable, MainTable[Client], MainTable[Country])

VAR Items =

    VALUES(MainTable[Item])

RETURN

    GENERATE(

        ClientsCountries,

        ADDCOLUMNS(

            Items,

            "AmountSold",

            CALCULATE(

                SUM(MainTable[AmountSold]),

                FILTER(

                    MainTable,

                    MainTable[Client] = EARLIER(MainTable[Client])

                        && MainTable[Country] = EARLIER(MainTable[Country])

                        && MainTable[Item] = EARLIER(MainTable[Item])

                )

            )

        )

    )

In the very first line SUMMARIZE(......), am I able to add fields from other tables which I have already created relationships for? 

Many thanks. 🙂

[Power_It_Up · Accepted solution]

Thanks @pankajnamekar25 for the quick reply.  By switching [AmountSold] to "Don't Summarize" does the trrick.  How can I convert the blanks to a zero or even text?  Thank you again. 🙂

Edit: Please ignore. I changed this line of code: 

Thanks again. 🙂 P.S. I may be back lol!

[miTutorials]

I Suggest doing this transformation in your source than doing this in PowerBI !!