How to use Power Pivot connection only data measures slicers in a dashboard

I have to create a interactive table dashboard showing Last 1 Week, Last 4 Week, Last 13 Week, Last 26 Week and Last 52 Week columns for a list of sales products. I need to show 6 different measures which need to populate in 6 different rows for each product in my pivot table and table dashboard. I have created the 6 measures which are populated in individual pivot table tabs.

I recently created a Power BI dashboard using the DAX code below which works and allows end user to switch between measures easily. I need to replicate this for Excel and getting stuck on the creating the correct formula for my slicer so when end user selects measure data table should change accordingly

Creating Data Table Code -

 SelectMeasure = DATATABLE ("Measure", STRING,{
    {"Dist (%)"},
    {"ROS (£)"},
    {"UROS"},
    {"Sales (£)"},
    {"Units"},
    {"Volume (Litres)"}})

Measures Format Code -

FormatString = SWITCH(
'SelectMeasure'[Measure],
"Dist (%)", "0.00%", // Show two decimal points
"ROS (£)", "£#,##0.00", // Show two decimal points
"UROS", "#,##0.00",  // Show two decimal points
"Sales (£)", "£#,##0.00K",  // Display in thousands
"Units", "#,##0.00K",  // Display in thousands
"Volume (Litres)", "#,##0.00MLtrs",  // Display in millions 
"0.00%"  // Default format string) 

Table Data Code -

Table Values = 
VAR SelectedCategory = SELECTEDVALUE('SelectMeasure'[Measure], "Dist (%)")
VAR VolumeValue = CALCULATE(SUM('Related Table'[CleanTotalVolumeInMl]) *       
SUM(Monthly[TotalItemUnit]))
RETURN SWITCH(SelectedCategory,
  "Dist (%)", FORMAT(DIVIDE(SUM(Monthly[TotalStoresItemSoldInMonth]), 
DISTINCTCOUNT('Suntory Call File'[Aquifer ID])), "0.00%"),
  "ROS (£)", FORMAT(AVERAGE(Monthly[ROS]), "Currency"),
  "UROS", FORMAT(AVERAGE(Monthly[UROS]), "0.00"),
  "Sales (£)", FORMAT(SUM(Monthly[TotalItemSales]), "Currency"),
  "Units", FORMAT(SUM(Monthly[TotalItemUnit]), "#,##0"),
  "Volume (Litres)", IF(ISBLANK(VolumeValue), BLANK(), FORMAT(VolumeValue / 1E6, 
"#,##0.00") & "MLtrs"))

I have tried to replicate the Power Bi statements with the following formulas but they dont seem to be working

Measures Format Code -

=IF(A2="Dist (%)", "0.00%",
IF( A3="ROS (£)", "£#,##0.00",
IF( A6="UROS", "#,##0.00",
IF( A4="Sales (£)", "£#,##0.00K",
IF(A5="Units", "#,##0.00K",
IF(A7="Volume (Litres)", "#,##0.00MLtrs",
"0.00%"  // Default format string))))))

Table Data Code - Using Direct Cell References

=IFS(B5 = "Distribution %", 'PivotTable_Distribution'!$B$4,
 B5 = "ROS", 'PivotTable_ROS'!$B$4,
 B5 = "Sales", 'PivotTable_Sales'!$B$4,
 B5 = "Units", 'PivotTable_Units'!$B$4,
 B5 = "UROS", 'PivotTable_UROS'!$B$4,
 B5 = "Volume", 'PivotTable_Volume'!$B$4,TRUE, "")

Table Data Code - Using Named Ranges

=IFS(B5 = "Distribution %", GETPIVOTDATA("Distribution
 %'PivotTable_Distribution'!$B$4),
 B5 = "ROS", GETPIVOTDATA("ROS", 'PivotTable_ROS'!$B$4),
 B5 = "Sales", GETPIVOTDATA("Sales", 'PivotTable_Sales'!$B$4),
 B5 = "Units", GETPIVOTDATA("Units", 'PivotTable_Units'!$B$4),
 B5 = "UROS", GETPIVOTDATA("UROS", 'PivotTable_UROS'!$B$4),
 B5 = "Volume", GETPIVOTDATA("Volume", 'PivotTable_Volume'!$B$4),TRUE, "")

Table Data Code - Using INDEX and MATCH

=IFS(B5 = "Distribution %", INDEX('PivotTable_Distribution'!$B$4:$B$100,        
 MATCH("Distribution %", 'PivotTable_Distribution'!$A$4:$A$100, 0)),
 B5 = "ROS", INDEX('PivotTable_ROS'!$B$4:$B$100, MATCH("ROS",  
 'PivotTable_ROS'!$A$4:$A$100, 0)),
 B5 = "Sales", INDEX('PivotTable_Sales'!$B$4:$B$100, MATCH("Sales", 
 'PivotTable_Sales'!$A$4:$A$100, 0)),
 B5 = "Units", INDEX('PivotTable_Units'!$B$4:$B$100, MATCH("Units", 
 'PivotTable_Units'!$A$4:$A$100, 0)),
 B5 = "UROS", INDEX('PivotTable_UROS'!$B$4:$B$100, MATCH("UROS", 
 'PivotTable_UROS'!$A$4:$A$100, 0)),
 B5 = "Volume", INDEX('PivotTable_Volume'!$B$4:$B$100, MATCH("Volume", 
 'PivotTable_Volume'!$A$4:$A$100, 0)),TRUE, "")

None of these have worked. What can I try next?