Return variables in rows instead of column

DAX DAX Calculations
1

Help… trying to get these three variables returned each in a row. The result will have Years in columns across the table and each of the three variables as a row showing quantity for each Year.

I get this error message: “The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.”

Here’s the DAX formula I am trying to write. Thanks for your help.

Net volume = 
VAR OilVol = [Net Oil (Mbbl)]
VAR GasVol = [Net Gas (MMcf)]
VAR NGLVol = [Net NGL (Mgal)]
RETURN
    ROW ( "Oil", OilVol, "Gas", GasVol, "NGL", NGLVol )
2

Any reason why you can’t just rotate the table and have the years in one column. This is far more optimal for creating visualisation inside of Power BI.

Then you could use;

SUMMARIZE( Sales, Dates[Years],

…then add all you VARs here within the summarize table

Would this work?

To me this would be better.

1 Like
3

It would work but the data is a lot with more variables and years. Below is sample of intended output…

Group Product 2017 2018 2019 2020 2021 2022 …… 2040
A Oil 5,100 6,400 4,000 5,500 3,200 3,700 3,300
Gas 5,300 800 6,900 3,900 5,600 7,700 1,700
Ngl 6,200 7,400 2,200 5,800 6,200 3,200 2,200
B Oil 2,900 5,600 7,100 1,300 5,200 300 4,800
Gas 6,100 6,000 4,500 800 3,700 4,500 4,700
Ngl 1,600 2,400 200 2,400 5,900 1,000 4,600
……
G Oil 1,500 400 200 2,500 2,800 2,900 7,500
Gas 3,100 7,100 5,600 3,400 4,800 6,700 2,800
Ngl 1,400 2,600 5,000 4,700 5,500 3,300 4,200
4

You really, really want this unpivoted for Power BI, where the years are in one column and the values are in one column.

When data is in this format in Power BI there is basically nothing you can do.

Check out this video to review what I mean here -

Sorry for pushing back here but I just know I’m right. This table structure is very sub-optimal.

Certainly let me know if I’m missing something big here, but I want to make sure I steering you in the right direction.

1 Like