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

Helper I

## Calculated column to contain the minimum value of another column

Hi

I am trying to get the minimum price of a part number over a period of time and populate a calculated column with this minimum price.

I am using the following DAX

MinimumPrice_GBP = CALCULATE(MIN(InvoiceDetail[UnitCost_GBP]),FILTER(ALLEXCEPT(InvoiceDetail,DimPeriod[Description],InvoiceDetail[DimPart NumberId]),InvoiceDetail[DimPart NumberId]=EARLIER(InvoiceDetail[DimPart NumberId])))

This however populates the calculated column with the minimum price of the part number per month.  Hence, the minimum price is different for each month.  I need the calculated column to contain the minimum price for the overall period selected, hence contain the same price for each month.

I am not sure how to post a screen shot in this message.

A simple example

Product A supplied by 3 different suppliers, in selected Period ID, 110, 112, 111.  In this example the Minimum price should be 1.25.

Product    Supplier            Price    Minimum Price   period

A               SupplierA         1.50           1.25                 110

A               Supplier B         1.25           1.25                 112

A               Supplier C         1.30           1.25                111

A               Supplier B          1.32            1.25               111

This is what is happening at the moment

Product    Supplier            Price    Minimum Price   period

A               SupplierA         1.50           1.50                 110

A               Supplier B         1.25           1.25                 112

A               Supplier C         1.30           1.30               111

A               Supplier B          1.32          1.30               111

Does anyone have any ideas?

Appreciate any help anyone can offer.

23 REPLIES 23
Super User

Hi,

Does this measure work?

=CALCULATE(MIN(Data[Price]),ALL(Data[Supplier]),ALL(Data[Period]))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

You could try these two formulas as below:

`Column = CALCULATE(MIN(InvoiceDetail[Price]),ALLEXCEPT(InvoiceDetail,InvoiceDetail[Product]))`

or

`Column 2 = CALCULATE(MIN(InvoiceDetail[Price]),FILTER(InvoiceDetail,InvoiceDetail[Product]=EARLIER(InvoiceDetail[Product])))`

Result:

Regards,

Lin Li

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.
Helper I

Unfortunately, this does not appear to work.

Create a new column with the below;

MinimumPrice1= CALCULATE(MIN(InvoiceDetail[UnitCost_GBP]),FILTER(InvoiceDetail,InvoiceDetail[DimPart NumberId]=EARLIER(InvoiceDetail[DimPart NumberId])&& InvoiceDetail[DimPeriodId]=EARLIER(InvoiceDetail[DimPeriodId])))

Period Dimension is a slicer.  It displays the Period Descriptions.  User can select one or more from this dimension.  Period id is connecting period dimension to the the fact table.

Its still only populates the minimum price field with the minimum price for each month rather than the minimum price for the overall period selected.

Community Support

Period Dimension is a slicer.  It displays the Period Descriptions.  User can select one or more from this dimension.

If what you want to create is a calculate measure not calculate column?

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://community.powerbi.com/t5/Desktop/column-vs-measure/td-p/13201

MinimumPrice1= CALCULATE(MIN(InvoiceDetail[UnitCost_GBP]),FILTER(InvoiceDetail,InvoiceDetail[DimPart NumberId]=EARLIER(InvoiceDetail[DimPart NumberId])&& InvoiceDetail[DimPeriodId]=EARLIER(InvoiceDetail[DimPeriodId])))

this is calculate column that calculates the minimum value of the same DimPart NumberId and the DimPeriodId

and if you want create a calculate measure you could use this formula like this

`Measure = CALCULATE(MIN(InvoiceDetail[Price]),ALLSELECTED(InvoiceDetail))`

here is pbix, please try it.

https://www.dropbox.com/s/kj3tn507xuhena0/test.pbix?dl=0

And I think this is a simple problem just need more details.

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.
Helper I

Hi Lin

Thank you for the test.pbix.  In your example, if we were to add more products, Product B, Product C.  Will your example show minimum price for each product?

Community Support

Yes, it will work well, for example:

You could try it on your pbix, if not your case, please share a simple sample pbix file, it will help us solve this case easily.

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.
Helper I

Hi Lin

Nearly there but not quite.

I would like the measure to show minimum price for each product with the period 110 and 111 selected.

For example

Product           Supplier  Period   Price    Measure

A                         a           110       1.50      1.30

A                         b            111       1.32     1.30

A                         c            111      1.30       1.30

B                         a            111      1.10       1.10

B                         b            111      1.20       1.10

C                         a            110       0.9         0.9

C                         b            111       1.30       0.9

Super User

Hi,

Have you tried my suggestion above?  Change ALL(Data[Period]) to ALLSELECTED(Data[Period])

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi

I have tried both examples by Lin and Ashish.  Both examples work in simple example put together by Lin.  However, when I add a product slicer, filter by one or more product aswell as period dimension, the minimum price goes all skew again.

Helper I

Helper I

In the example below, the Minimum price for product A should be 130 and Product B should be 1.00 for periods; 110, 111.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Super User

Hi,

This one works

=CALCULATE(MIN(InvoiceDetail1[Price]),ALLSELECTED(InvoiceDetail1[Price]),ALLSELECTED(InvoiceDetail1[Period]),ALLSELECTED(InvoiceDetail1[Supplier]))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

I was expecting to see £3501.4739 for all U009 Part Numbers.

Community Support

£3501.4739 is min(price) for the whole table, not only for all U009 Part Numbers. and this involves a measure total problem.

here is a post for you refer to.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and for your case, could share us a Simulated pbix with the same slicers and all the fields that will in the table visual include expected output in many cases. That will be of great help to us in solving the problem.

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.
Helper I

Hi Lin

https://www.dropbox.com/s/qx50dm70ja2difk/test.pbix?dl=0

Helper I

In my live example, the part number is taken from part number dimension, supplier is taken from Supplier dimension and the Period is taken from the period dimension.

The fact table (Invoice Detail) has the following relationships;

InvoiceDetail(DimPeriodId)   to DimPeriod(Id)                                             Period Slicer -  DimPeriod(Description)

InvoiceDetail(Dim Part NumberId)  to DimPartNumber(Id)                         Part Number Slicer -  DimPartNumber (Part Number)

InvoiceDetail(DimSupplierNewId) to DimSupplier(id)                                  DimSupplier(Supplier)

Helper I

https://www.dropbox.com/s/qx50dm70ja2difk/test.pbix?dl=0

I have altered the simple example to simulate my live example.

Super User

Hi,

Does this work?

=CALCULATE(MIN(InvoiceDetail1[Price]),ALLSELECTED(InvoiceDetail1[Price]),ALLSELECTED(InvoiceDetail1[Period]),ALLSELECTED(Supplier[SupplieDesc]),ALLSELECTED('Product'[ProdDesc]))

Regards,
Ashish Mathur
http://www.ashishmathur.com

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