Forum Discussion

sthandiwe's avatar
sthandiwe
Frequent Visitor
4 years ago
Solved

Create table with measures as values

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])
    )
 
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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's avatar
    MFelix
    4 years ago

    Hi sthandiwe ,

     

    You should put last part of the names:

    Budget

     5 Day PNL

    Triggger

     

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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's avatar
      sthandiwe
      Frequent Visitor
      4 years ago

      Hi Felix

      What do I put in the types of measures table?

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi sthandiwe ,

         

        You should put last part of the names:

        Budget

         5 Day PNL

        Triggger