The easiest thing might be to use Tableau's Group feature to combine the In Progress and Lender Selected amounts.
Ctrl-click to select both fields, right-click > Group.
Now you'll have three rows and one column:
Status Amount In Progress & Lender Selected 454 Closed 1,303 Dead 181
Hmm I see, this is my goal...
In Progress is made up of multiple loans. Im trying to take the loans that make up In Progress and divide them by the total In Progress Amount ($210). This will help me calculate a weighted average.
Thus, I am trying to figure out how to calculate the grouped total, so I use that value in an equation.
1 of 1 people found this helpful
I'm still a little fuzzy on your goal, but you can use an OR function in your original formula
Amount In Progress and Lender Selected =
IF [Status] == 'In Progress' OR [Status] == 'Lender Selected' THEN [Amount] END
This will be evaluated for every row and you can SUM() the measure to get the total.
As with any other regular measure, this new field will be partitioned, or aggregated, by dimension pills in the view. If, for example, you put the Status on the Rows shelf (instead of Status (group)), you're going to get the sum for In Progress and Lender Selected individually (210 and 244, respectively). If this is a problem for you, you can workaround this with a table calc---a primary use case for table calcs is to allow different levels of aggregation in a view.
Amount In Progress and Lender Selected TC =
WINDOW_SUM(SUM(Amount In Progress and Lender Selected))
When adding the table calc, right-click and set Compute Using / Addressing to Status. Now you'll get the combined 454 value for all Status rows.
Sorry for all the back and fourth, I tried your equation but it was giving me a Null error.
I have attached an updated image that should hopefully clarify.
You can use a quick table calculation to calculate the percent of total for each category.
Click the SUM(Amount) pill and select Quick Table Calculation > Percent of Total. By default it will calculate the percent based on the total for all categories, but you can change this. Click on the pill again > Edit Table Calculation > Compute Using and select Property. Now the table calc will be partitioned by other dimensions in the view; in this case Status.
You can see the underlying formula by clicking on the SUM(Amount) table calc pill and selecting Edit Table Calculation. If you don't want the 100% values to show on the sub-totals, you can add some logic
IF SUM([Amount]) / TOTAL(SUM([Amount])) < 1 THEN SUM([Amount]) / TOTAL(SUM([Amount])) END
PercentOfTotal.twbx.zip 35.2 KB
I replicated the calculations you provided in your workbook. However my % of total is:
1. Dividing by the Sum of all the groups
2. Or if I change the calculation to Compute Using > Property, the table does not display any values
Can you take a look at the attached workbook?
Book_Loan_Amount.twbx.zip 35.2 KB
Here is your layout -- the main difference from my example above is that you have two additional dimensions, Fund to the left and Lender to the right of Property:
Ninety percent of the complexity of table calcs is setting the partitioning and addressing (compute using) settings.
I sometimes feel that Tableau over complicates this by providing several methods. You can
- Select a single dimension for addressing (by, for example, selecting Compute Using > Property). In this case the table calc is partitioned by all other dimensions in the view. In your example, when I select Compute Using > Property, the table calc is still being partitioned by Lender, which means the TOTAL(SUM(Amount)) table calc is summing all of the members of the Property dimension for each Lender. If you remove Lender from the view the table calc will work.
- Select multiple dimensions using Table(down), Table(across), ..., settings. Table(down) is the default in your case and results in all of the dimensions on the Row shelf being used for addressing, which means that the table calc is summing the Amounts for all Status, Fund, Property, Lender.
- Configure partitioning and addressing using Edit Table Calculation > Compute Using > Advanced. Tableau calls this "advanced", but I often find it the easiest to use, since you can explicitly set what dimensions are used for partitioning and what dimensions are used for addressing.
To calculate the sum for each Status above, we want the table calc to use all of the values for Fund, Property and Lender. Click the Amount of Percent Of Total field and select Edit Table Calculation > Compute Using > Advanced. Move Fund, Property, and Lender to the Addressing side. (While it doesn't matter with a TOTAL(SUM()) aggregation, Addressing is hierarchical, so ideally you want the fields on the addressing side to be in the same order you have them in the view.)
Another nice feature of the Edit Table Calculation dialog box is the Description of the compute using; here: Totals summarize values from Fund, Property, Lender for each Status.
And now, I think, you have the result you want:
Thank you so much ... that makes a lot more sense now. This is probably the reason why the amount of each loan is being divided by the total sum of all the status (instead of each particular one). I'm having that issue on my regular spreadsheet that also has additional dimensions.
I assume I would have to apply similar logic to my windowsum calculation?
Yes, you'll need to set the partitioning and addressing for all table calcs, including WINDOW_xxx() functions. Measures that include table calcs have a triangle symbol on the pill.
Again, I usually set the partitioning / addressing explicitly, with either Compute Using > dimension or with the advanced dialog box. One small downside with this approach is that if you remove a field from the view that was used in the addressing (such as Lender above), the measure and related fields will turn red, indicating an error (all addressing fields need to be in the view).