topic Re: Multiply Calculation in Subtotal in Desktop

Multiply Calculation in Subtotal

hanami001 — Wed, 28 Feb 2018 06:26:10 GMT

Hi Everyone,

I am using multiple formulas to calculate filed but I found in a Subtotal column is incorrect.

On column: Deduct Rate, I use formula = Amount_THB * Number of CPN. The value in row B, H, V and Y is correct but row Subtotal is incorrect. I want to row Subtotal is calculating = row: B+H+V+Y, please advise me.

Deduct Rates = SUMX('Interline Rate',[Amount_THB])*SUMX(vw_nw_sale_info_01,[Number of CPN])

Thank you.

Re: Multiply Calculation in Subtotal

Anonymous — Wed, 28 Feb 2018 06:50:49 GMT

Try:

First create this

Deduct Rates Measure = 'Interline Rate'[Amount_THB] * 'vw_nw_sale_info_01'[Number of CPN]

Now you can use

Deduct Rates = SUMX(
        VALUES('Interline Rate'[Routing_BI]),
        [Deduct Rates Measure]
)

Re: Multiply Calculation in Subtotal

hanami001 — Wed, 28 Feb 2018 06:54:39 GMT

Hi Ross73312,

Thank you for your reply.

I forget attached relationship of tables.

I cannot relation between 'vw_nw_sale_info_01'[Number of CPN] and 'Interline Rate'[Amount_THB].

Re: Multiply Calculation in Subtotal

Anonymous — Wed, 28 Feb 2018 23:25:36 GMT

That might be the killer part to your problem. You will need to figure out a way to relate the data if you wish to do this. Conceptually, is there anything that relates the two tables?

Re: Multiply Calculation in Subtotal

hanami001 — Tue, 06 Mar 2018 06:34:24 GMT

Hi Ross73312,

This problem is solved.

I have used another way, an insert a new column and use the formula to get data, an example is below.

Deduct Rates = LOOKUPVALUE('Interline Rate'[Amount_THB],
   'Interline Rate'[Carrier],vw_nw_sale_info_01[Carrier Uplifted],
   'Interline Rate'[Routing_Bi],RELATED('Routing Bi'[Route_Bi]),
   'Interline Rate'[RBD],vw_nw_sale_info_01[CLASS]
)

% Change Deduct = [Revenue] - [Deduct Rates]

Re: Multiply Calculation in Subtotal

Anonymous — Tue, 06 Mar 2018 06:35:53 GMT

Glad you figured out a path to a solution :)