How can I recreate an Excel 'table' using columns and measures without the data in rows?

[bitofanewb]

Hi,

I've been asked to recreate lots of Excel tables (not actual tables, unsure how else to describe them) using Power BI. I've already created hundreds of count columns in Excel Power Query (all rows are either 1 or 0) before pulling the data into Power BI to help with this. The column names are the name of what the row should actually be, plus the month they're counting (i.e. Starts Apr, Prev Yr Starts Apr, FiL Apr, Prev Yr FiL Apr and so on). What I need is to be able to use these columns as rows, and have the months as columns. I need to also create the following rows, which I've done for FiL Apr to show the required calculations: Performance vs Budget (

CALCULATE(SUM('Board Pack'[FiL Apr]) - CALCULATE(SUM(Bud_Other[Apr-24]), Bud_Other[Budget Type] = "Funded in Learning", Bud_Other[Area] = "Total"))

), % Variance to Budget (CALCULATE((SUM('Board Pack'[FiL Apr]) / CALCULATE(SUM(Bud_Other[Apr-24]), Bud_Other[Budget Type] = "Funded in Learning", Bud_Other[Area] = "Total")) -1)

), and RAG Status (

     SWITCH(

        TRUE,

        [FiL Apr % Variance to Budget] >0, "Green",

        [FiL Apr % Variance to Budget] <= -0.1, "Amber",

        "Red"))

).

The budget data comes from a separate query, which is just the data needed pasted into a table (not an actual table), it is already in the rows as needed, the columns are named Apr-24, May-24 and so on (can easily be renamed if that would help).

This is what the 'tables' I need to recreate look like on a basic level: 

Any help or suggestions on how I could tackle this would be greatly appreciated.

Thanks in advance for reading.

[bhanu_gautam]

@bitofanewb 

Unpivot Columns in Power Query:

Load your data into Power BI.
In Power Query Editor, select the columns that represent the months (e.g., Apr, May, Jun, etc.).
Right-click and choose "Unpivot Columns". This will transform your data so that the months become rows.

Create the necessary measures for your calculations

DAX
Performance vs Budget =
CALCULATE(
SUM('Board Pack'[FiL Apr]) -
CALCULATE(
SUM(Bud_Other[Apr-24]),
Bud_Other[Budget Type] = "Funded in Learning",
Bud_Other[Area] = "Total"
)
)

DAX
% Variance to Budget =
CALCULATE(
(SUM('Board Pack'[FiL Apr]) /
CALCULATE(
SUM(Bud_Other[Apr-24]),
Bud_Other[Budget Type] = "Funded in Learning",
Bud_Other[Area] = "Total"
)) - 1
)

DAX
RAG Status =
SWITCH(
TRUE,
[% Variance to Budget] > 0, "Green",
[% Variance to Budget] <= -0.1, "Amber",
"Red"
)

Add a new matrix visualization to your report.
Drag the necessary fields to the Rows, Columns, and Values areas of the matrix.
For example, you can drag the unpivoted month column to Columns, the measure names (Actual, Budget, Prev Yr, etc.) to Rows, and the corresponding values to Values.

[bitofanewb · Accepted solution]

Hi, 

Thanks for the quick reply.

Unfortunately I can't pivot the columns as they are as there's 14.5k rows of data.

I'm going to try creating a table using lookup values with the months as columns, and the rows selecting the correct value for the applicable month. I can adjust the DAX I create for Apr to return the applicable month too.