Forum Discussion
Calculating between rows and columns
Hi everyone,
I need help to solver this problem:
Table - 1
| Code | 01/01/2020 | 01/02/2020 | 01/03/2020 | 01/04/2020 | 01/05/2020 | 01/06/2020 | 01/07/2020 | 01/08/2020 | 01/09/2020 |
| 100500001 | 10 | 5 | 10 | 20 | 12 | 16 | 18 | 20 | 20 |
Table - 2
| Code | Qty per to |
| 100500001 | 0.5 |
Expected result
| Date | Code | T. Qty |
| 01/01/2020 | 100500001 | 5 |
| 01/02/2020 | 100500001 | 2.5 |
| 01/03/2020 | 100500001 | 5 |
| 01/04/2020 | 100500001 | 10 |
| 01/05/2020 | 100500001 | 6 |
| 01/06/2020 | 100500001 | 8 |
| 01/07/2020 | 100500001 | 9 |
| 01/08/2020 | 100500001 | 10 |
| 01/09/2020 | 100500001 | 10 |
Thanks in advanced
I created a dummy dataset
I used the DAX formula
It has changed the columns into rows here.
It should work for your case to unpivot using DAX. Create a new table under Table Tools:
Data_New = UNION(
SELECTCOLUMNS('Table - 1', "Code", 'Table - 1'[Code], "Date", "01/01/2020", "Qty", 'Table - 1'[01/01/2020]),
SELECTCOLUMNS('Table - 1', "Code", 'Table - 1'[Code], "Date", "01/02/2020", "Qty", 'Table - 1'[01/02/2020]))
You might have to finish the formula until your last date, which in this case that I'm seeing, 01/09/2020.
Once your new table is created, create a new column with LOOKUPVALUE:
Qty_per_to = lookupvalue('Table - 2'[Qty per to], 'Table - 2'[Code], 'Table - 1'[Code])
Then, create another column:
T.Qty = [Qty]*[Qty_per_to]
5 Replies
What I would do is unpivot Table - 1.
Power Query > Select your columns to unpivot > Transform tab > Unpivot Columns
Then, do a merge (in Power Query) or a lookup (in DAX), Table - 1 with Table - 2 using [Code] as the key and the result is [Qty per to]. Once you've done that, you can do a multiplication column on [Qty per to] with the value that was unpivoted.
First of all, thanks for your post!
Actuallly, I can't use this way of solution. I need do this without "power query or pivot".
I would like to use dax functions to get this result.
Using just lookvalue isn't enough becasuse I have to turn in column in rows (the dates are in columns).
Anyway thanks.
If you can't use Power Query to unpivot your data, take a look at this thread
https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax
Then, apply the same steps I've mentioned after you used DAX to unpivot.
