Forum Discussion
Combine multiple measures to create a formatted table
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.