Using FIXED () and Variable?

verytiredgirl

I'd like to create a BeastMode 'MTD %' that can:

1. Calculate the % of each GL account based of Total Sales
2. Can be dynamically filtered by Month and Year variables

Here's my MTD % Beast Mode:

SUM(`Amount`)/ (SUM(CASE WHEN `Class 3` = 'Sales' THEN `Amount` ELSE 0 END) FIXED ())

And my MTD Beast Mode:
SUM(CASE
WHEN Fiscal Year= Year Selector AND Month Selector = Month Name THEN (Amount*-1)
ELSE 0
END)

I just need to incorporate the MTD in the MTD % but it didn't work

ArborRose

How about

CASE

  WHEN `Fiscal Year` = `Year Selector`

    AND `Month Selector` = `Month Name`

  THEN 

    SUM(`Amount`) 

    / 

    SUM(CASE WHEN `Class 3` = 'Sales' THEN `Amount` ELSE 0 END)

END

verytiredgirl

I tried, and I got this error

ArborRose

I didn't realize this was on a pivot table. Yes, the calculations must be full aggregated with the case logic contained in the aggregate. I don't have a sample dataset to test with. How about this?

MTD:

SUM(

  CASE

    WHEN `Fiscal Year` = `Year Selector`

      AND `Month Selector` = `Month Name`

      AND `Class 3` = 'Sales'

    THEN `Amount`

    ELSE 0

  END

)

MTD %:

CASE

  WHEN SUM(

    CASE

      WHEN `Fiscal Year` = `Year Selector`

        AND `Month Selector` = `Month Name`

        AND `Class 3` = 'Sales'

      THEN `Amount`

      ELSE 0

    END

  ) = 0 THEN NULL

  ELSE 

    SUM(

      CASE

        WHEN `Fiscal Year` = `Year Selector`

          AND `Month Selector` = `Month Name`

        THEN `Amount`

        ELSE 0

      END

    ) 

    / 

    SUM(

      CASE

        WHEN `Fiscal Year` = `Year Selector`

          AND `Month Selector` = `Month Name`

          AND `Class 3` = 'Sales'

        THEN `Amount`

        ELSE 0

      END

    )

END

verytiredgirl

Thank you for the response, yes I can provide a sample dataset with random numbers:

Book1.xlsx

For my Pivot Table, I just dragged Class 1, 2, 3 into Rows, and MTD, MTD % Beast Modes into values

I tried the new MTD Beastmode above, I was only able to returned the Sales Amount and not other GL Account Amount, hence the MTD % won't work because it's not gonna be able to calculate the % of Sales for other.