Forum Discussion

hanami001's avatar
hanami001
Frequent Visitor
8 years ago
Solved

Multiply Calculation in Subtotal

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 va...
Need Help
  • hanami001's avatar
    hanami001
    8 years ago

    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]

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

 

 

  • hanami001's avatar
    hanami001
    Frequent Visitor
    8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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?

      • hanami001's avatar
        hanami001
        Frequent Visitor
        8 years ago

        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]