What is wrong with this expression?

[Tiger1]

SUM(select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [Customer Number] = [_THISROW].[Customer Number]))

Customers_Aging_Exclude_Int = Current Table
Customer Balance = Column in the table
Monthly_Activity_DSO = another table
Final Trx Amount = Column in that table

Here is the error:
Column Name ‘Test DSO by branch’ in Schema ‘Customers_Aging_Exclude_Int_Schema’ of Column Type ‘Decimal’ has an invalid app formula ‘=SUM(select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [Customer Number] = [_THISROW].[Customer Number]))’. Arithmetic expression ‘(CUSTOMERS_AGING_EXCLUDE_INT[Customer Balance]/SUM(MONTHLY_ACTIVITY_DSO[Final Trx Amount]))’ has inputs of an invalid type ‘Unknown’

Column Types:
[Customer Balance] = Decimal
[Test DSO by Branch] = Decimal
[Final Trx Amount] = Price
[Customer Number] = Number

[WillowMobileSys]

Tiger:

select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [Customer Number] = [_THISROW].[Customer Number])

Your inner SELECT() with a division by SUM() doesn’t make sense within a SELECT(). What are you trying to accomplish here?

[Tiger1]

I am trying to find what is called a DSO value:

Total balance / Total Payments * days in that particular month

I have all these values but in two separate tables. I was able to do this using this expression:

SUM(Customers_Aging_Exclude_Int[Customer Balance])/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31

However, I am now trying to break this down PER branch. Branch is a column in my table. I want to be able to select a branch and then see the DSO per that branch.

[WillowMobileSys]

Tiger:

However, I am now trying to break this down PER branch. Branch is a column in my table.

So I’m confused. I don’t see anything in your original expression that is using the Branch column.

What table is Branch in?

What is the relationship between Customer Number and Branch?

[Tiger1]

I am sorry. I was building a Dashboard. I have one table (grouped by Branch) to allow the user to select a branch. Then once they select the branch, i have a detail view from the same table - using that expression i have - to show the DSO per that branch.

Branch is a column in the first table (the table with the expression). Customer Number does not have to do with branch.

Oh wait. that is my problem…

I need to change the expression to:

SUM(select(Customers_Aging_Exclude_Int[Customer Balance]/SUM(Monthly_Activity_DSO[Final Trx Amount]) *31, [branch] = [_THISROW].[branch]))

Correct?

[WillowMobileSys]

You’ll still see the error because of the way you are using SUM within the select.

Tiger:

Total balance / Total Payments * days in that particular month

You provided this formula above. Would it be better defined as:

Total balance by Branch / Total Payments by Branch * days in that particular month?

If so, how do you get?

Total balance by Branch
Total Payments by Branch

[Tiger1]

Oh. You are right. I need to make a VC to get that first… Then i will use the formula you provided. Thanks

[Tiger1]

So i now have this:

[Total Balance by Branch] / Monthly_Activity_DSO[Total Trx Amount by Branch] * 31

[Total Balance by Branch] = VC in Table A
[Total Trx Amount by Branch] = VC in Table B (Monthly_Activity_DSO)

How do i get this to work?

Gives me this:
Arithmetic expression ‘([Total Balance by Branch]/MONTHLY_ACTIVITY_DSO[Total Trx Amount by Branch])’ has inputs of an invalid type ‘Unknown’

[WillowMobileSys]

Tiger:

Monthly_Activity_DSO[Total Trx Amount by Branch]

You would need to perform some SELECT(), LOOKUP(), or have some Ref column to get the value from Table B.

But I wonder, why wouldn’t you just create the VC for [Total Trx Amount by Branch] in Table A where you will be using it?

[Tiger1]

If i do - do i Ref the other table from there?

This is [Total Trx Amount by Branch]:
SUM(SELECT(Monthly_Activity_DSO[Final Trx Amount], [Sales Territory] = [_THISROW].[Sales Territory]))

[Tiger1]

I moved that expression to Table A. But now i am having an issue of selecting by branch (I created a Dashboard with a table of branches and a detail view of this new DSO by branch VC).

It doesnt work. No data shows when i click/choose a branch

[WillowMobileSys]

Well, I thought the SUM was by Branch? Now [Sales Territory] is in the mix. If it was just by Branch then you could have just copied the expression over to Table A.

Now I am concerned you are mixing Summation results.

Why are you Summing by [Sales Territory] and not by Branch as was originally discussed above?

[Tiger1]

Sales Territory is branch = i just re name it in the Description.

The Raw data says [Sales Territory] = but my company likes to see “Branch”

[WillowMobileSys]

Ok, what is it called on Table A?

[Tiger1]

Same thing in both Tables. So i change the Display Name in both.

These are the three VC expressions i have so far:

[Total Balance by Branch]
SUM(Customers_Aging_Exclude_Int[Customer Balance])

[Total Trx Amount by Branch From Monthly]
SUM(SELECT(Monthly_Activity_DSO[Final Trx Amount], [Sales Territory] = [_THISROW].[Sales Territory]))

[DSO by Branch]
[Total Balance by Branch] / ([Total Trx Amount By Branch from Monthly] *31)

[WillowMobileSys]

Ok, then it should work. After you created the VC in Table A, did you re-sync the data. The VC updates on a Sync.

[Tiger1]

Yes - i think something is wrong with the [Sales Territory] - referecing the Location Table ( i had to create to change the Sales Territory from a number to a name)

i.e.
Sales Territory 54 = Midland

[WillowMobileSys]

Yes, you need to make sure your data rows are properly updated so the values match between the tables.

[Tiger1]

This is the table for changing Sales Territory into the actual name.

But when i click on the Name -
nothing changes in the detail view below:

Is this correct:
[Total Balance by Branch]
SUM(Customers_Aging_Exclude_Int[Customer Balance])

Or does it need to have [Sales Territory] in it somewhere?

[WillowMobileSys]

It should probably be basically the same format as the expression you copied from Table B…just adjusted for Table A and Customer Balance. So yes, it needs to have Sales Territory, at least based on what I understand of your data.