Using FIXED () and Variable?

verytiredgirl
verytiredgirl Member
edited October 31 in Beast Mode

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

image.png

Tagged:

Answers

  • ArborRose
    ArborRose Coach

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • verytiredgirl
    verytiredgirl Member
    image.png

    I tried, and I got this error

  • ArborRose
    ArborRose Coach

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • verytiredgirl
    verytiredgirl Member
    edited November 1

    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.

Categories