Thank you very much for sharing the workbook with your sample data. I reviewed the workbook and noticed that all of the Measures are not row level values but instead these are aggregate values for the Quarter Code, Quarter Dates, and office. As a result, the calculations need to be either ATTR() or AVG() calculations to show correctly per row. I also do not see a field named "Net Available Hours Difference YTD from Prior Month" in the workbook and I see a few different fields that might represent the one desired. Could you clarify the field desired? In addition, if you know the values that would exist in the view - this would help to share since they would enable me to verify that the values derived are correct for what you are looking for?
Guessing here but window_sum(avg([Net Available Hrs])) - I think this gets that value and also thinking you might be trying to get the difference between quarters rather than months which could be:
(avg([Net Available Hrs])-lookup(avg([Net Available Hrs]),-1))/lookup(avg([Net Available Hrs]),-1)
I changed all of the measures to utilize Measure Names/Values instead of displaying each as a separate column. Hopefully this helps but knowing a bit more about the desired field and what value is expected would help me to help you more.
Tableau example 1_pv.twbx 173.4 KB
What I am seeking is that the Net Available Hours Difference YTD from Prior Month be displayed as it currently is, as a numeric value, and then ALSO as a percent. I had it working last month before the quarter changed. When the quarter changed it threw off the table calculation, and I had to add to that cal in order to keep the numeric value displayed correctly, however that workaround I believe is what is causing the percent to not calculate properly now.
The Net Available Hours Difference YTD from Prior Month (Net YTD field) calculation is now as follows:
(Run along table across)
WINDOW_SUM(SUM([Net Available Hrs]), -2, 0) - WINDOW_SUM(SUM([Net Avail Hrs (Arch)]))
Getting to the percent should be simple, NET YTD / Net Avail Hrs (Arch) = percent
But, it doesn't calculate correctly, the percent is not mathematically correct when you check what Tableau returns.
I think that it has do with -2, 0 added to the table cal, pretty sure that is it because like I said, we had the percent working before the quarter changed earlier this month using the calculation above (Net YTD / Net Avail Hrs (Arch)).
And, yes this dashboard is not "correct" in how we've used all kinds of fields, and workarounds to get to what we needed. And, the data is not the most friendly to begin with, agree!
I need to revert the example you sent back me because we are still in 9.1.1. I will do this and add on to anything I may have missed in my reply to you as soon as able.
In the meantime, I hope that the above has made what I am seeking more clear.
Thank you! Angie