Making the MIN value a constant or passing it to another measure

[vazperen]

I am trying to use the MIN result in other measures.  I have a list of products that were sold over several week.  In order to find the MIN it was suggested to use:

BM = MINX(SUMMARIZE(Table1,[Products],"MIN",[Sales]),[MIN])

That worked perfectly fine to find the MIN for the average sales in products in a sumarized filtered table by date range.  The result is 52.33.

The question is how do I pass the value in another measure?  What I want to do is to take the average sales for each product minus the MIN value.

An example would be:

Product A minus MIN: 73.50 - 52.33 = 21.17

Product B minus MIN: 52.33 - 52.33 = 0

The MIN is dynamic as it changes based on filters applied to the table. Thanks!

[Melvlee · Accepted solution]

Hi Vazperen,

A quick win is to create a duplicate Sales table. It's meant for calculating BM so it can just have the necessary columns.

Then in your main Sales table, you can create a measure to work out (Avg Sales - BM). 

In this way, BM is always the same value as the duplicate table has no relationship with the main Sales table.

Hope this helps.

[Ashish_Mathur]

Hi,

=[Sales]-[BM]

Does this work?

[vazperen]

No, because the BM column that you see is the average sales for each product; therefore, =[AvgSales]-[BM] gives me zero for all products. 

[Ashish_Mathur]

So then it should be

=[Your sales measure]-[BM]

[vazperen]

The problem is BM.  BM is holding the values of each product.  If you see the formula fro BM it is sumarrizing the sales by product, then looking up the MIn out of that virtual column; therefore, it is not holding 52.33, but the average sales by product.  Maybe I need to change how to figure out the MIN in a different way.

[Ashish_Mathur]

The only way i can help you is if you share a link of your file.  Also, show the expected result there.