Ok. I made it this now:
SUM(SELECT(Customers_Aging_Exclude_Int[Customer Balance], [Sales Territory] = [_THISROW].[Sales Territory]))
And yet it is still not working…
Test the pieces. Show the VC’s in your view and confirm they are getting proper Summed values.
By “not working”, I need to know what you mean
I cant show them - because everytime i click on a branch (Sales Territory) there is nothing there. How can i show them?
BTW - this shows up if NOTHING is selected (meaning i do not select a branch).
But… when i select ANY branch it goes back to blank:
Ok, how are you building the list you are using for Branch selection?
You need to make sure the value used on the selected Branch row matches the “Sales Territory” values on the rows in Table A and Table B.
Yeah. I thought i did. Here is the Table I built to handle the Location (Sales Territory). I built this to be able to put the label on the Name instead of having numbers (key):
Here is a sample of the data i get. If you look at column “Sales Territory” you will see those numbers…
So i am still not sure why it does not work.
Here is the Table ( i built for location) in Appsheets. I put the key as the “Key” and the label is on “Location”. I am not sure what else to do.
How is your view “Testing DSO by Branch” constructed? Is it a Dashboard? And if so, what are the views you have included?
Ok, you have an Interactive Dashboard. so the Detail view will filter based on the the selection of Branch.
What does your data table for “Customers_Aging_Exclude_Int” look like?
I don’t see an issue with the way the interactive portion is setup. But remember a Detail view is designed to display only a single row at a time. Your data has multiple rows with the same Sales Territory, so there will be multiple rows in the filter based on the Branch selection - which one is actually shown in the Detail view you can’t really guess at.
Having said that, the VC’s are on each row and should have the same values so it doesn’t really matter which row is shown in the Detail view.
Here’s what I would do to test. Create a Table view for the same data and then add that to the Dashboard as well - maybe replace the Detail view.
Try the Dashboard again and select a Branch to see if you get rows shown in the Table view. This will let you know if the filtering is working correctly or not.
Ok. Thanks let me try that and see what happens…
Ok. I did that. Do i need to group by location?
So data is coming through - but again when i click on a branch - nothing. But if i do not choose a branch:
I want to add- i do not think the DSO values are correct.
I want to add this as well. I use this formula to calculate the DSO for the entire company (Not based on branch) it works fine.
SUM(Customers_Aging_Exclude_Int[Customer Balance])/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31
Ok - i figured out why the values were wrong. I had the () in the wrong place:
Correct way - [Total Balance by Branch] / ([Total Trx Amount By Branch from Monthly]) *31
Wrong way - [Total Balance by Branch] / ([Total Trx Amount By Branch from Monthly] *31)
However - i still cant figure out why BY BRANCH doesnt work
So - i rebuilt the table Location (branch) table. It works now! thanks for all your help.