Calculated column to contain the minimum value of another column

[BhawnaPanchal]

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.

[Ashish_Mathur]

Hi,

Does this measure work?

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

Hope this helps.

[v-lili6-msft]

 hi, @BhawnaPanchal

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

[BhawnaPanchal]

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.

[v-lili6-msft]

hi, @BhawnaPanchal

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

and for your formula 

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.

Could please do a simple sample pbix file and share it expected output. This will help you find the answer quickly.

Best Regards,

Lin

[BhawnaPanchal]

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?

[v-lili6-msft]

hi, @BhawnaPanchal

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

[BhawnaPanchal]

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

[Ashish_Mathur]

Hi,

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

[BhawnaPanchal]

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.

[BhawnaPanchal]

[BhawnaPanchal]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[BhawnaPanchal]

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

[Ashish_Mathur]

Hi,

This one works

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

Hope this helps.

[BhawnaPanchal]

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

[v-lili6-msft]

hi, @BhawnaPanchal

£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

[BhawnaPanchal]

Hi Lin

Please see attached simulated pbix.

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

[BhawnaPanchal]

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)

[BhawnaPanchal]

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

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

[Ashish_Mathur]

Hi,

Does this work?

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