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

Anonymous
Not applicable

## How to calculate total cost for lowest prices?

Hi all,

I have a table called 'Proposal' simiilar to this one:

 Supplier DestinationGroup Destination Price Units Min Price A 1 1 55 10 55 A 1 2 43 20 35 A 1 3 65 15 65 B 1 1 68 10 55 B 1 2 35 20 35 B 1 3 87 15 65

where Min Price is calculated like this:

```Min Price =
CALCULATE (
MIN ( Proposal[Price] ),
FILTER (
ALLSELECTED ( Proposal ),
Proposal[Destination] = MAX ( Proposal[Destination] )
&& Proposal[DestinationGroup] = MAX ( DestinationGroup )
)
) ```

My goal is to calculate total cost per supplier, but taking only minimum prices, like in 'Total Cost' column :

 Supplier DestinationGroup Destination Price Units Min Price Total Cost A 1 1 55 10 55 550 A 1 2 43 20 35 A 1 3 65 15 65 975 B 1 1 68 10 55 B 1 2 35 20 35 700 B 1 3 87 15 65 2225

Does anyone have an idea how to calculate this?

Best,

Stefan

1 ACCEPTED SOLUTION
Community Support

hi, @Anonymous

You could use this formula as below:

```Measure = var _table=CALCULATETABLE(ADDCOLUMNS(Proposal,"_minprice",[Min Price] ),FILTER(ALLSELECTED(Proposal[Price]),[Price]=[Min Price])) return
SUMX(_table,[_minprice]*[Units])```

Result:

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Resolver II

I tested this with your data set and it seems to work.

```Total Cost =
VAR FindCost =
IF(
[Min Price] = SELECTEDVALUE( Proposal[Price] ),
[Min Price] * SUM( Proposal[Units] ),
BLANK()
)
RETURN
IF(
SELECTEDVALUE( Proposal[Supplier] ) = BLANK(),
CALCULATE(
SUMX( Proposal, Proposal[Price] * Proposal[Units] ),
FILTER( Proposal, Proposal[Price] = [Min Price] )
),
FindCost
)```
Anonymous
Not applicable

Thank you @MitchM , it is working as a charm! 🙂

It's just that I am not able to summarize it by supplier,when I try to create a table Supplier by Total Cost, expected result is:

 Supplier Total Cost A 1525 B 700 2225

but I get blank values for rows:

 Supplier Total Cost A B 2225

Do you have a solution for this behavior?

Thank you!

Stefan

Community Support

hi, @Anonymous

You could use this formula as below:

```Measure = var _table=CALCULATETABLE(ADDCOLUMNS(Proposal,"_minprice",[Min Price] ),FILTER(ALLSELECTED(Proposal[Price]),[Price]=[Min Price])) return
SUMX(_table,[_minprice]*[Units])```

Result:

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you @v-lili6-msft ! Though it work on my test data, in production it doesn't sum up correctly. Do you have an idea on what should I pay attention to and try to debug it?

Although @MitchM 's solution is correct to, this is more complete one. My mistake is that I wasn't clear enough what the goal is in the begining.

Thank you both!

Best,

Stefan

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 - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors