How to combine SUMX and LOOKUPVALUE formulas? Getting sytax errors...

[raphazzz]

Hi there,

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

I have two tables:

table A

and table B

* 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!

Raphazzz

[Ashish_Mathur]

Hi,

Create a matrix visual and drag this measure

Total = sum(Data[Qty])

Hope this helps.

[v-yangliu-msft]

Hi  @raphazzz ,

Here are the steps you can follow：

1. Go into Power Query – Copy TableA to form TableB.

2. TableB – Select Column [month] -- Transform – Pivot Column.

3. In the Pivot Column interface – select the column [qty].

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mikelytics]

HI @raphazzz 

before you do anything you should bring table b in a normalized structure using the unpivot column feature in Power Query:

Unpivot columns - Power Query | Microsoft Learn

Afterwards you should have two table of the same kind and you can combine them in Power Query or with a relation and more.

Before:

unpivoting

after

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[jgeddes · Accepted solution]

Here is a DAX formula that you can use to create a calculated table that will resemble Table B

stateTableLayout2 = 
SUMMARIZE(
    stateTable,
    stateTable[state], 
    "Jan", CALCULATE(SUM(stateTable[qty]), ALLEXCEPT(stateTable, stateTable[state]), stateTable[month] = "jan"),
    "Feb", CALCULATE(SUM(stateTable[qty]), ALLEXCEPT(stateTable, stateTable[state]), stateTable[month] = "feb"),
    "Mar", CALCULATE(SUM(stateTable[qty]), ALLEXCEPT(stateTable, stateTable[state]), stateTable[month] = "mar"),
    "Apr", CALCULATE(SUM(stateTable[qty]), ALLEXCEPT(stateTable, stateTable[state]), stateTable[month] = "apr")
)

You would just have to keep adding rows in for the remaining months.
The end result looks like...

Of course you can do the same thing "easier" in Power Query by creating a new query referenced from your Table A and then Pivoting the [month] column and selecting Sum as the aggregation.