I think I'm understanding your problem, but if not, let me know.
1. To create "Count of Negative Stores":
2. I was a little confused on this due to your wording. Your original post said to do a distinct count of stores with Profit YTD <> 0 (not equal to), but I assumed you meant less than 0. So this formula does that, then divides by the "Count of Negative Stores":
One more thing that confused me--did you want to factor in the Parameter Month/Year for the 2nd calculation?
Regardless, to get your desired output, Count all stores = COUNTD([Store Desc])
Count stores with negative profit = [Count of Negative Stores]
% Stores with Negative Profit = [% of Negative Stores]
You have an older version of Tableau, so unfortunately I can't attach the workbook I modified.
Hi Walt, thank you very much for looking at this. I could have been clearer in my request so apologies.
Count of Negative Stores
When counting the stores with negative profit, the profit amount needs to be a running sum profit amount based on the selected Month/Year parameter.
The Profit YTD calculation in my workbook is monthly.
How can these aggregate & non-aggregate arguments work for this formula?
% Negative Stores
Yes the Month/Year should be factored in please. This calc also needs to take the running sum of the Profit based on the Month/Year selected.
based on Month/Year selected, COUNTD the Store Desc where the running sum of the Profit amount <> 0 (does not equal zero). This will give a distinct count of stores that have had - or + profit. then divide by the Count of Negative Stores.
In the workbook provided, Store A is a good example for what I need. Screenshot below. The running sum is positive so the Count should be 0.
Appreciate your time on this. Though the business is not yet at the latest version of Tableau I do have access to the latest Tableau desktop so all good to send back a file.
Thanks for the clarification, I think I follow what you want.
1. First, we want to calculate the YTD running profit based on the Parameter Month/Year, so the easiest way I found was to use a FIXED calculation:
2. Next, we want to count the negative stores, based on the above calculation, but also factoring in whether the Reason is blank or not (hence the 2nd FIXED calculation):
3. Next, we want to calculate the number of stores where sales <> 0, so we actually reference the first calculation we created:
4. Lastly, create the % of Negative Stores calculation, using the formulas from Step #2 and #3
I believe this works as you want it to. If it doesn't, reply back and let me know!
Neg Profit Stores.twbx 29.4 KB
Hi Walt, the solution you provided above is working brilliantly. I now need a way to count the negative stores over a (relative date) rolling last 12 month period, and wondered if you could help? This count of the negative stores
- shouldn't reference the month/year parameter ( I would apply a date filter 'relative' month for last 12 months).
- should take into account the running YTD sum of the Profit (within Year i.e.YTD running sum starts again for the next year).
Need to show the # negative stores by month (trend graph) for the last 12 months - so it will go across years.
I've put a screenshot below showing the output, but need it to go back into last year.
If you can assist, would this be enough information to proceed? or I can mock up a file to attach.