FIXED Function with filter on card

_DanielB_
_DanielB_ Member
in Charting

Hi All

I'm sure this has been asked a few times but I couldn't find a specific answer that helped with my challenge

I have a PIVOT Table which contains all of my financial data rows consisting of Costs and Revenues

I want to calculate a share of revenue for each cost whilst having a filter on my card to remove the Revenue line however when I filter out revenue all the shares drop to 0

I've not used FIXED functions to level before so could use some help and guidance on how to show share of revenue % on my table and filter out the revenue row

Thanks

Dan

Tagged:

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    The fixed function in Beast Mode lets you compute a value across a fixed level of aggregation, ignoring filters on specific rows. Using a beast mode to represent Total Revenue (fixed)

    FIXED (SUM(CASE WHEN `Type` = 'Revenue' THEN `Amount` ELSE 0 END))

    and use that in another formula for % Share

    CASE 
    
  WHEN `Type` = 'Cost' THEN `Amount` / FIXED (SUM(CASE WHEN `Type` = 'Revenue' THEN `Amount` ELSE 0 END))
    
  ELSE NULL
    
END

    Filter out Type = 'Revenue' and the table should show cost rows.

    Cost Category

    Amount

    % of Revenue

    Labor

    50,000

    10.0%

    Software

    25,000

    5.0%

    Btw, I don't have any sample data created to confirm this works.

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

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    The fixed function in Beast Mode lets you compute a value across a fixed level of aggregation, ignoring filters on specific rows. Using a beast mode to represent Total Revenue (fixed)

    FIXED (SUM(CASE WHEN `Type` = 'Revenue' THEN `Amount` ELSE 0 END))

    and use that in another formula for % Share

    CASE 
    
  WHEN `Type` = 'Cost' THEN `Amount` / FIXED (SUM(CASE WHEN `Type` = 'Revenue' THEN `Amount` ELSE 0 END))
    
  ELSE NULL
    
END

    Filter out Type = 'Revenue' and the table should show cost rows.

    Cost Category

    Amount

    % of Revenue

    Labor

    50,000

    10.0%

    Software

    25,000

    5.0%

    Btw, I don't have any sample data created to confirm this works.

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

  • _DanielB_
    _DanielB_ Member

    Hi @ArborRose, I had to move the FIXED function to the end of the BM as it returns a syntax error when placed at the start.

    Also, when I filter out the 'Type' = Revenue on the table itself, the BM show 0 for everything which I know is incorrect

    Incidentally, the one I've created is no good as it naturally ignores filters so my calculation is showing a much lower % than it should when I use said filters.

    Is there a way to create a fixed revenue based on selected YEAR and selected MONTH as these are 2 separate slicers allowing users to choose a combination of YEAR and MONTH independently

    Thanks

    Dan

  • ArborRose
    ArborRose Coach

    I don't typically work with FIXED, so we're traveling outside my knowledge zone. It might help to post it as a new question with anonymized sample data in csv.

    I think what you want is something like a fixed revenue across year and month like this

    Fixed Revenue by Year-Month:

    FIXED (BY `Year`, `Month`) SUM(
    
  CASE WHEN `Type` = 'Revenue' THEN `Amount` ELSE 0 END
    
)

    And a calculated field to get the percent share

    CASE WHEN `Type` = 'Cost' THEN 
    
  `Amount` / `Fixed Revenue by Year-Month`
    
ELSE
    
  NULL
    
END

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

Categories