Solved: Function to calculate the SUM of total sale

Izzat_Safrah · 12-26-2019 01:40 AM

In the column list, I have 2 tables, “ORDER ID” , “STORE”

Inside the table “ORDER ID” ,there is column [TOTAL SALES FOR BILL]
also there is key column [PURCHASING INVOICE NO].

Inside the table “Store”, there is column [NET SALE]
also there is column [PURCHASING INVOICE NO]. This is taking reference from the column in the table above.

I need a function to calculate the SUM of total sale in the column [NET SALE]. Condition: choose matching values in column [PURCHASING INVOICE NO].

I tried the following function inside the column [TOTAL SALES FOR BILL] in the table “ORDER ID” :

Select(STORE[PURCHASING INVOICE NO].[PURCHASING INVOICE NO],Sum([NET SALE]))

And it didn’t work.

Can you help me with the correct function please.

Kirk_Masden

I can see that you have not used SELECT() correctly. Here’s the basic syntax:

SELECT( from-dataset-column , select-row? [ , distinct-only? ] )

Also, the location of the SUM() is not correct.

Let’s start with a simple use of SELECT():

SELECT(STORE[NET SALE],TRUE)

This produces a “list” of all of the values for [NET SALE]. The condition is “TRUE” so we haven’t narrowed it down yet.

To get the sum of such a list of values, you need put the list inside the sum expression:

SUM(SELECT(STORE[NET SALE],TRUE))

Now, the next step is to write the condition for the part that currently has “TRUE”.

I don’t understand what you are trying to do so I can’t write the condition. Do the two tables have the same structure with the same keys?

I will be traveling tomorrow so I may not be able to respond. I hope, though, that what I have written here helps you make some progress. Good luck!

View solution in original post

Kirk_Masden

I can see that you have not used SELECT() correctly. Here’s the basic syntax:

SELECT( from-dataset-column , select-row? [ , distinct-only? ] )

Also, the location of the SUM() is not correct.

Let’s start with a simple use of SELECT():

SELECT(STORE[NET SALE],TRUE)

This produces a “list” of all of the values for [NET SALE]. The condition is “TRUE” so we haven’t narrowed it down yet.

To get the sum of such a list of values, you need put the list inside the sum expression:

SUM(SELECT(STORE[NET SALE],TRUE))

Now, the next step is to write the condition for the part that currently has “TRUE”.

I don’t understand what you are trying to do so I can’t write the condition. Do the two tables have the same structure with the same keys?

I will be traveling tomorrow so I may not be able to respond. I hope, though, that what I have written here helps you make some progress. Good luck!

Izzat_Safrah

@Kirk_Masden

Its work now with this formula:

SUM(SELECT(STORE[NET SALE],[PURCHASING INVOICE NO]=[_THISROW].[PURCHASING INVOICE NO]))

Thank you so much for your help

Kirk_Masden

Great! I wouldn’t have been able to come up with the complete expression, as you have done, but I’m glad that what I wrote helped you get to a solution!! Good luck with your app!