Forum Discussion

Power_It_Up's avatar
Power_It_Up
Icon for Helper II rankHelper II
1 year ago
Solved

Create a new table from the Main Table with criteria

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. 🙂

 

 

 

Need Help
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

     

    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's avatar
    Power_It_Up
    1 year ago

    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!

     

     

4 Replies

  • miTutorials's avatar
    miTutorials
    Icon for Super User rankSuper User
    1 year ago

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

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

     

    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's avatar
      Power_It_Up
      Icon for Helper II rankHelper II
      1 year ago

      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!

       

       

    • Power_It_Up's avatar
      Power_It_Up
      Icon for Helper II rankHelper II
      1 year ago

      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. 🙂