Create table with measures as values

[sthandiwe]

Hi All 

Happy new year. I have searched the community for this issue, but I am not finding anything to help. 

I have a table (matrix) that looks like this:

That I need in the table format like this:

The budget is a MIN(budget) value from the table, 
The 5 Day PnL values are measures all calculated in a similar fashion: FI 5 DAY PnL act BW = CALCULATE( SELECTEDVALUE(BW[ROLLING_FI]), FILTER(BW, BW[TRANSDATE]=[SelectedDate BW]))

The trigger values are measures all calculated in a similar fashion: FI TRIGGER act BW = CALCULATE( SELECTEDVALUE(BW[TRIGGER_FI]), FILTER(BW, BW[TRANSDATE]=[SelectedDate BW]))

I have tried to use the UNION to create the second table, but the values are not showing:

Table =

UNION(

ROW ("Component", "FX", "BUDGET", MIN(BW[FXBudget]),"5 DAY PnL", [FX 5 DAY PnL act BW], "TRIGGER", [FX TRIGGER act BW], "DATE", [SelectedDate BW]),

ROW ("Component", "FI", "BUDGET", MIN(BW[FIBudget]),"5 DAY PnL", [FI 5 DAY PnL act BW], "TRIGGER", [FI TRIGGER act BW],"DATE",[SelectedDate BW]),

ROW ("Component", "TOTAL", "BUDGET", MIN(BW[BudgetLCY]),"5 DAY PnL", [SUM 5 DAY PnL act BW], "TRIGGER", [SUM TRIGGER act BW],"DATE", [SelectedDate BW])

    )

[MFelix · Accepted solution]

Hi @sthandiwe ,

For this you need to create two tables:

• Component
• Type of measure

Then using a SWTICH function you must add the measures you need to the calculation something similar to:

Metric by component =

SWITCH( TRUE(),
SELECTEDVALUE(Component[Table]) = "Fi" && SELECTEDVALUE(MeasureType[Column]) = "Budget",MIN(BW[FIBudget]),
SELECTEDVALUE(Component[Table]) = "Fx" && SELECTEDVALUE(MeasureType[Column]) = "Budget",MIN(BW[FxBudget]),
...
)

Then just make the necessary adjustments for each of your metrics

[MFelix · Accepted solution]

Hi @sthandiwe ,

For this you need to create two tables:

• Component
• Type of measure

Then using a SWTICH function you must add the measures you need to the calculation something similar to:

Metric by component =

SWITCH( TRUE(),
SELECTEDVALUE(Component[Table]) = "Fi" && SELECTEDVALUE(MeasureType[Column]) = "Budget",MIN(BW[FIBudget]),
SELECTEDVALUE(Component[Table]) = "Fx" && SELECTEDVALUE(MeasureType[Column]) = "Budget",MIN(BW[FxBudget]),
...
)

Then just make the necessary adjustments for each of your metrics

[sthandiwe]

Hi Felix

What do I put in the types of measures table?

[MFelix · Accepted solution]

Hi @sthandiwe ,

You should put last part of the names:

Budget

 5 Day PNL

Triggger