What is wrong with this expression?

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

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

1 Like

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.

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?

1 Like

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?

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

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

1 Like

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

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’

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?

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]))

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

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?

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”

Ok, what is it called on Table A?

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)

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.

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

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

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?

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.