FIXED Function with filter on card

_DanielB_ · May 20

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 · May 20

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 · May 27

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

ArborRose · May 20

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.

_DanielB_ · May 27

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 · May 27

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

FIXED Function with filter on card

Best Answers

Answers

Categories