5 Replies Latest reply on Feb 3, 2017 9:00 AM by Jim Dehner

Calculating a Profit Factor

Hi,

My data source displays data in a daily format. One of the columns is profit/loss. So in other words it shows daily profit/loss. The data in the profit/loss column includes positive, negatives, and 0.

What I'd like to be able to do is display on my dashboard what's called a "Profit Factor" which is achieved by taking sum of profit divided by sum of loss. The profit factor number must be positive and exclude the 0's.

I've been able to accomplish this by making a separate column - Gross Profit and Gross Loss (presented as positive) and then created an LOD FIXED calculation. {SUM([Gross Profit])}/{SUM([Gross Loss])}. This gives me the profit factor for the complete set of data and the answer I'm looking for however when I'm adjusting through the filters on my dashboard, I would like this number to change according to my filters.

1) Any suggestions to how to accomplish this in Tableau? This Profit Factor should also be affected by filters.

2) Is there a more elegant approach to my data so I don't need to have a separate gross profit and gross loss column?

Thank you!

Henry

• 1. Re: Calculating a Profit Factor

Good morning Henry

Not sure I fully understand your goal -

But I think the LOD calculation is not needed - just a simple calculation Gross Profit/Gross Loss will suffice

Attached is a T10.1 workbook based on superstore data

Let me know if this helped

Jim

• 2. Re: Calculating a Profit Factor

1) Any suggestions to how to accomplish this in Tableau? This Profit Factor should also be affected by filters.

AW - Put the filters to context if using FIXED

2) Is there a more elegant approach to my data so I don't need to have a separate gross profit and gross loss column?

AW - Profit and Loss will have to be extracted separately, not necessarily as columns but in calculated fields

Agree with Jim, there might not be need for LOD calcs here, it all depends how you intend to use it. Without the LODs you don't need filters in context, which should give a performance gain.

• 3. Re: Calculating a Profit Factor

Hi Henry,

Find my approach as reference below and attached in workbook version 9.3

• 4. Re: Calculating a Profit Factor

Thanks a lot Jim and to all.

Fixed was helpful for me too to get another measure but you're right I made it slightly over complicated.

This met my needs