Not sure if I understand your problem...
Based on the first picture, the second one can be created without any specific calculations by dragging Project CPI into rows and hours into the show me/text shelf, which will sum it automatically. If you then filter on the other two columns, wouldn't that work?
Or does your data not look like the first image? Perhaps it is more clear with some sample/dummy data?
Thanks so much for replying. However, your recommendation is not giving me the total sum for each CPI depending on the filters selected. I want the Y Axis to show me the SUM of Actual MH for each CPI depending on the RQ and/or Site selected. Here is the result by using your recommendation:
Thanks again! Please let me know if you need more data/clarification.
Thanks for the screenshot - that does help to make things clearer, but I'm still not sure if I fully understand the screenshot you're showing vs what you describe.
From what I can see, you have filtered on one particular RQ and one particular Site (filter shelf in grey). Then you've further filtered to one particular CPI (filter shelf in blue). The graph is showing you the sum total of Actual MH (as per rows shelf) for each unique combination of Program, Project, RQ and Site (because they're on your level of detail shelf - there seem to be two combinations of them, which is why you get two points).
What are you expecting to see?
Why do you have Program, Project, RQ and Site on your level of details shelf?
From what I understand that you described initially you want to do the following:
See sum total of Actual MH for each CPI, optionally grouped/filtered by RQ and/or Site.
To do that I'd put Actual MH in the rows (as you have done), CPI in the columns (as you have done as well). Clear the Level of Detail box of all it's contents, because I don't think there is a reason to include it (I may of course be wrong if any calculation on the sheet uses those fields, but that is not what you described I think and none of the fields you are using/have mentioned seem to be calculations at all). Then you can filter, group (using column shelf) or colour as per RQ and/or Site.
Could it be that in the current screenshot you have two dots instead of one in your graph because there are either two programs or two projects matching the filters you have selected?
Hope this helps!
We are trying to calculate the sum of hours for all projects with equal CPI. I have attached the excel file. Please use the Consolidated tab for the raw data and the GPO tab for the graphic and then look for SUM Hours (You will need to expand some columns to find it).
The SUMIF translation in few words is this:
If (RQ and Site and Program) = All
Then sum (CPI range, CPI, Actual MH range)
If (RQ <> All and Site=All and Program=All)
Then sum (Actual MH range, CPI range, CPI, RQ range, RQ)
If (RQ <> All, Site <> All, Program = All)
Then sum (Actual MH range, CPI range, CPI, RQ range, RQ, Site range, Site)
If (RQ <> All, Site <> All, Program <> All)
Then sum (Actual MH range, CPI Range, CPI, RQ range, RQ, Site range, Site, Program range, Program)
If (RQ = All, Site <> All, Program = All)
Then sum (Actual MH range, CPI range, CPI, Site range, Site)
Then sum (Actual MH range, CPI range, CPI, Site range, Site, Program range, Program)
I added some of the fields to the level of detail for the tooltip but if you think it might be causing problems, I will remove it. I have added some of them as well to Context to help me with the calculations. CPI should not be one of the filters at the end. I have been using it to verify the final values from the excel spreadsheet.
Thanks in advance for your help
Data and Mock Up.xlsx 146.8 KB
Wow that SumIf looks massively complicated. To be honest I'm either missing the point there or it is more complicated than it needs to be for Tableau (different for Excel of course). If you have data in Tableau and calculate a sum total of stuff... like hours and then filter it... the sum is automatically done for the filters. What you filter out will not be counted in the calculation.
So when I put the workbook together by simply dragging in the hours field and letting it total, and then I filter, I get the same values as I can see in your excel sheet (thanks for adding that btw - it was really helpful to just play with the data).
Percentage of total does the same: it sees the "total" as "everything you have not filtered out". Again that gives me the same numbers as your sheet.
It looks slightly different because the way axis values are chosen is a little different from Excel (I mean different ranges than excel would pick), but I verified the numbers and they look the same.
I made it in version 8 first because we just moved to that, but then realised from your screenshot you're probably on 7 still. The dashboards look better in V8 if you have it
Can you please verify the data and see if you can see if it does something you don't expect it to do?
I've not looked into the tooltip yet - do let me know what you want in there that required the level of detail to be used.
Looking forward to your reply hahaha we'll get it sorted at some point...
Trial for Elizabeth.zip 498.9 KB
Thank you very much! You are the best! I will take a look and will let you know if I have any questions.
One million thanks again!
I was able to follow your recommendations and my tableau dashboard is almost ready. However, I am not sure how to calculate the Weighted Average and the Delta because again, these formulas are coming from Excel (they are using the SUMPRODUCT formula). Please check the GPO tab in the original excel file for the details. In addition, I had trouble trying to accommodate the dual axis and now it is showing the Actual MH on the right! Could you please help me to understand what happened? If I accommodate the values on the Rows shelf to make the SUM (Actual MH) to be listed first, the two marks (bars in grey and black) are not displayed Finally, I want to add the Project Name to the tooltip, but if I add it to the level of detail shelf, it messes up the calculations. If I try to edit the tooltip, the dimension is not listed in the insert drop down list.
Thanks again for your help
CPI and MPI Version2.twb.zip 24.0 KB
You can change the left and right axis by dragging the pills. If I drag the pill for the right axis to the left of the other one (make sure you see the little blue triangle to indicate it will go to the left) they will change around without any issues. If you right-click on the axis you have an option to "Move Marks to Front" or "Move Marks to Back". Since you have two bars on top of each other, you need to move the smaller one to the front.
I just added Project name to the CIP - Hours Distribution chart and nothing seemed to change. So not sure what you mean there. If there is a one-on-one relationship with what you've already calculated it shouldnt make a difference. If each bar in the chart could be multiple project names, then I'd recommend rethinking the requirement.
The weighted average was nearly right, but needed a SUM in there, to make sure you use the totals for the group - ie the sum of hours*cpi divided by the sum of hours:
sum(([Actual MH]*[CPI]))/sum([Actual MH])
As for the Delta... I got a bit lost there in the calculations and things are a bit busier now, so I'm not sure when I have time to look at this
Sorry it took a while to get back to you... hope it helps,
Thank you very much for helping me with this. I was able to fix the issue with the axis! Thank you! For the project, what i need is the Project Name listed in the tooltip when the user selects an specific value. I inserted the Project Name in the Level of Detail shelf but by doing that, the calculations get messed up. What would you recommend me to be able to see the Project Name in the dashboard?
With regards the Delta, I figured out the formula, I guess.... but then when I try to calculate the WAV Delta and the WAV CV things get worse because I am trying to use mix fields: aggregated and non-aggregated. This is what I used for the Delta: ABS([Weighted Average]-AVG([CPI]))
To make easier for you to understand the WAV, the delta, the WAV Delta and the WAV CV, here is the summary of the formulas in my own words .... thanks again for your help!!
Weighted Average CPI
1) (Actual MH * CPI) = Weight CPI
2) SUM(Weight CPI)
3) SUM(Actual MH)
4) Weighted Average CPI = SUM(Weight CPI)/SUM(Actual MH)
In Tableau: SUM(([Actual MH]*[CPI]))/SUM([Actual MH]) This is done, working OK
If Percentage of %Total Actual MH = 0 then " "
else ABS(Weighted Average CPI-CPI)
Note: Since we are mixing aggregate and non-aggregate, I tried to make the CPI aggregated by adding the AVG.
In Tableau: ABS([Weighted Average CPI]-AVG([CPI])). Not sure if the formula is correct. Please confirm.
Weighted Average Delta and Weighted Average CV
1) (Actual MH * Delta) = Weight Delta
2) SUM(Weight Delta)
3) SUM(Actual MH)
4) Weighted Average Delta = SUM(Weight Delta)/SUM(Actual MH)
5) Weighted Average CV = Weighted Average Delta/Weighted Average CPI
In Tableau: To be determined ....????
I know that you are busy but whenever you have a chance, please review these formulas and let me know if you need any further clarification. These are the last 3 formulas in my entire dashboard.
Thanks in advance for your help!
CPI and MPI Version3.twb.zip 25.3 KB