Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

How to calculate total cost for lowest prices?

Hi all, 

 

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

SupplierDestinationGroupDestinationPriceUnitsMin Price
A11551055
A12432035
A13651565
B11681055
B12352035
B13871565

 

 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 :

SupplierDestinationGroupDestinationPriceUnitsMin PriceTotal Cost
A11551055550
A12432035 
A13651565975
B11681055 
B12352035700
B13871565 
      2225

 

Does anyone have an idea how to calculate this?

 

Best,

Stefan

 

1 ACCEPTED SOLUTION

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:

11.JPG

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.

View solution in original post

4 REPLIES 4
MitchM
Resolver II
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:

SupplierTotal Cost
A1525
B700
 2225

 

but I get blank values for rows:

SupplierTotal Cost
A 
B 
 2225

 

Do you have a solution for this behavior?

 

Thank you!

Stefan

 

 

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:

11.JPG

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

Helpful resources

Announcements
Europe Fabric Conference

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

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors