Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  How to combine SUMX and LOOKUPVALUE formulas? Getting sytax errors...

    Posted Dec 09, 2022 11:49 AM

    Hi there,

     

    I have the following situation, and I couldn't find a solution to my problem.

     

    I have two tables:

     

    table A

    raphazzz_0-1670604285137.png

     

     

    and table B

    raphazzz_1-1670604338026.png

     


    * The values in the columns from Jan to Dec were input manually just for reference

     

    Which formula or how do I return the sum of the values from column qty on table A to table B based on column state?

     

    I tried this formula below, but I'm getting syntax errors.

    jan = SUMX(
    CALCULATETABLE(
    'table A',
    'table A'[month] = "jan",
    'table A'[state] = LOOKUPVALUE( 'table A'[state], 'table A'[qty], table B[jan])
    ),
    ''table A'[qty]
    )

     

    Thanks!



    ------------------------------
    Raphael Jaber
    Business Analyst
    ------------------------------


  • 2.  RE: How to combine SUMX and LOOKUPVALUE formulas? Getting sytax errors...

    Posted Dec 21, 2022 01:22 AM
      |   view attached
    Hi Raphael Jaber,

    You can refer the my attached file. I used Summarize function.


    ------------------------------
    Hai Huynh
    ------------------------------

    Attachment(s)

    pbix
    Summarize to combine.pbix   25 KB 1 version


  • 3.  RE: How to combine SUMX and LOOKUPVALUE formulas? Getting sytax errors...

    Posted 27 days ago

    Hi Raph,

    Did measure      SUM([qty])       not work?
    Matrix :
    Column - Month
    Rows - State
    Value - Measure
    ?

    Regards,



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------