cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
Frequent Visitor

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

5 REPLIES 5
Anonymous
Not applicable

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

Frequent Visitor

Hi Ross73312,

I forget attached relationship of tables.

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

Anonymous
Not applicable

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?

Frequent Visitor

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
Not applicable

Glad you figured out a path to a solution 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors