Trying to get different values into the pivot table

Analyst_Mike
Analyst_Mike Member
in Beast Mode

Below is the code I am using in Beast mode. I want the "Not" metrics to be divided by MM to give me a value, and the MLR to be divided and the rest of the metrics to be total values.

The pivot table is giving me total values.

SUM(CASE
WHEN MOS_Year = 2023 THEN
CASE
WHEN Metric = 'Member Month' THEN MM
WHEN Metric = 'MLR' THEN COST_MLR / REV_MLR
WHEN Metric = 'Shared Savings Dollars' THEN PAID
ELSE
CASE WHEN Metric NOT IN ('MLR', 'Shared Savings Dollars') THEN PAID / MM
END
END
END)

Best Answer

  • ggenovese
    ggenovese Coach
    Answer ✓

    Hi @Analyst_Mike

    You may want to try this update, if it doesn't work it might be helpful to share a screenshot of your pivot table so that we can see how it's configured: 

    CASE
    
WHEN MOS_Year = 2023 AND Metric = 'Member Month' THEN SUM(MM)
    
WHEN MOS_Year = 2023 AND Metric = 'MLR' THEN SUM(COST_MLR) / SUM(REV_MLR)
    
WHEN MOS_Year = 2023 AND Metric = 'Shared Savings Dollars' THEN SUM(PAID)
    
WHEN Metric NOT IN ('MLR', 'Shared Savings Dollars') THEN SUM(PAID)/ SUM(MM)
    
END

Answers

  • ggenovese
    ggenovese Coach
    Answer ✓

    Hi @Analyst_Mike

    You may want to try this update, if it doesn't work it might be helpful to share a screenshot of your pivot table so that we can see how it's configured: 

    CASE
    
WHEN MOS_Year = 2023 AND Metric = 'Member Month' THEN SUM(MM)
    
WHEN MOS_Year = 2023 AND Metric = 'MLR' THEN SUM(COST_MLR) / SUM(REV_MLR)
    
WHEN MOS_Year = 2023 AND Metric = 'Shared Savings Dollars' THEN SUM(PAID)
    
WHEN Metric NOT IN ('MLR', 'Shared Savings Dollars') THEN SUM(PAID)/ SUM(MM)
    
END

Categories