Table with Metrics the Use Different Scenarios


Userlevel 1
Badge +2

Hello! I am seeking to make 1 Table that has 2 metrics (image below). The metrics are “P&L” and “Commentary”.

For the P&L metric I am seeking to show two different scenarios (“Actuals” and “Q2 Outlook”)

For the Commentary metric I am seeking to show ONLY 1 scenario. 


Is there any way to achieve this? It seems like the Scenario page selectors apply to ALL metrics in a table.

Curious if others have found get arounds for this?

 


4 replies

Userlevel 2
Badge +7

Hello @connorsmith ,

Here is my approach:

  1. Create a new Dimension that includes “P&L” and “Commentary”.

     

  2. Create a new metric that consolidated Metric P&L and Metric Commentary into this new metric.

     

  3. Create a Filter Metric with dimension Item (new created) and Scenario.

     

  4. Add the Filter Metric and the combined metric to the table, and hide the Filter Metric.

     

  5. Finally, configure the table's filter.


    Here is the result:
     

    Hope this is helpful to you,
    Regards,
    Weining

Userlevel 3
Badge +6

Hi @Weining Ben,

That’s a great idea but in the case of @connorsmith the P&L will be numeric and Commentary will be a text so you will have a data type issue.

I might have find another solution below :

Assuming you have 2 metrics with both 3 dimensions (PL3 Account, Department Group, External Cost Center)

  1. Commentary
  2. P&L

Your commentary metric should have a specific formula to feel only for the Actuals Scenario so cells aren’t blank but just empty.

""[ADD:'Department Group',Month,'PL3 Account']

 

Then you can leverage the Hide empty columns filter.

The only caveat I see is the size of the metric since all cells will be populated. However, based on your screenshot all 3 dimensions (PL3 Account, Department Group, External Cost Center) seems to have reasonable number of items, so it shouldn’t impact the performance.

 

Let me know if that was helpful.

Userlevel 1
Badge +2

Hi @Weining Ben thanks for your input. This is a very creative idea, but is due to Text and Number data types this might not be possible

Hi ​​​@CDALMAY thank you! this works 

FYI
@Not Jeremy Stern 

Userlevel 2
Badge +7

Hi @connorsmith , @CDALMAY ,
You are right, my method can only be applied to cases with a single data type.
Thank you for your proposed solution @CDALMAY 

Additionally, here is a related idea for your reference.

 

Reply