How to calculate total cost for lowest prices?

[Anonymous]

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

[MitchM]

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]

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

[v-lili6-msft · 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:

Best Regards,

Lin

[Anonymous]

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