FIXED Function with filter on card

_DanielB_

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

ArborRose

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.

ArborRose

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.