Combine multiple measures to create a formatted table

[Anonymous]

Hello BI Gods,

I have created multiple measures which are dependent on the slicer dates the user has entered. The user chooses a start and end date they want to analyse transactions over. My measures calculate the start value, change in value over time, end value for multiple variables, i.e:

Start Payment, Change in Payment, End Payment                          <-- 3 measures for Payment 

Start Receipt, Change in Receipt, End Receipt                                <-- 3 measures for Receipts

Start Settlement, Change in Settlement, End Settlement                <-- 3 measures for Settlement

All measures result in a $value

I want to create a table that displays the following from the results:

	Start Amount (USD)	Change over time (USD)	End Amount (USD)
Measure1	1234	-234	1000
Measure2	0	999	999
Measure 3	984	84	900

I can't think of a way to create this table in BI with my measures. It would be lovely if I could just drag and drop the values into their respective slot and then add row and column titles but no 😞 

Perhaps you can create a column called Start Amount and add measures start payment, start receipt and start settelment in? And do the same for change in and end amount. This then would allow me to create the table over.

Any ideas, BI Gods?

[technolog]

Step 1: Create a Calculated Table
First, create a calculated table to structure your measures. This table will include rows for each measure and columns for "Start Amount", "Change over time", and "End Amount".

Calculated Table
SummaryTable =
DATATABLE(
"Measure", STRING,
"Start Amount", DOUBLE,
"Change over time", DOUBLE,
"End Amount", DOUBLE,
{
{"Payment", [Start Payment], [Change in Payment], [End Payment]},
{"Receipt", [Start Receipt], [Change in Receipt], [End Receipt]},
{"Settlement", [Start Settlement], [Change in Settlement], [End Settlement]}
}
)
Step 2: Create a Matrix Visual
Add a Matrix visual to your report.
Drag the "Measure" column from the SummaryTable to the Rows well.
Drag the "Start Amount", "Change over time", and "End Amount" columns from the SummaryTable to the Values well.
Step 3: Create Individual Measures (Optional)
If you can't directly use the measure calculations inside DATATABLE, you can create individual measures for each amount and then refer to them in a calculated table.

Example Measures
Start Payment Measure = [Start Payment]
Change in Payment Measure = [Change in Payment]
End Payment Measure = [End Payment]

Start Receipt Measure = [Start Receipt]
Change in Receipt Measure = [Change in Receipt]
End Receipt Measure = [End Receipt]

Start Settlement Measure = [Start Settlement]
Change in Settlement Measure = [Change in Settlement]
End Settlement Measure = [End Settlement]
Step 4: Use Measures in Calculated Table (Alternative Method)
If using DATATABLE is not feasible due to limitations in referencing measures directly, use another approach:

Create a Calculated Table with Measures
SummaryTable =
UNION(
ROW("Measure", "Payment", "Start Amount", [Start Payment Measure], "Change over time", [Change in Payment Measure], "End Amount", [End Payment Measure]),
ROW("Measure", "Receipt", "Start Amount", [Start Receipt Measure], "Change over time", [Change in Receipt Measure], "End Amount", [End Receipt Measure]),
ROW("Measure", "Settlement", "Start Amount", [Start Settlement Measure], "Change over time", [Change in Settlement Measure], "End Amount", [End Settlement Measure])
)
Step 5: Display the Table in the Matrix Visual
Add a Matrix visual.
Add "Measure" to Rows.
Add "Start Amount", "Change over time", and "End Amount" to Values.