Need Help: Find the minimum and maximum of Net Price Per Unit for selected period

[_ajinkyaj]

I want to calculate the variance of Net Price Per Unit over the selected period from (YTD, 1Y, 2Y); 

Month-Year/Product Name	Ship To Name	Jan-2024	Feb-2024	Apr-2024
Product A	ST 1	$58		$54.92
Product A	ST 2	$54.92	$54.92
Product B	ST 4	$56.70		$59.25
Product B	ST 5	$56.70
Product C	ST 4	$57.70	$57.70	$60

above table is shown in matrix visual;
want Product Wise Min and Max for example: For Product A : we have 2 Ship To's 6 cell values (contains 2 blanks) Min $54.92 and Max is $ 58.
same goes for Product B: $56.70 and $59.25 and for Product C we have 3 values so $57.70 and $60.

I am able to find Globle min and Max using below dax

Min or Max Product-ST = 
    var valueDisplayed = 
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(Table 1, Table 2[Product Name], Table 3[Ship To Name], Date[Month-Year]),
                "@Net Price Per Unit", [Selected Period Net Price Per Unit]),
                ALLSELECTED()
        )
    var min_value = 
        MAXX(
        valueDisplayed,
        [@Net Price Per Unit]
        )
    
RETURN
    IF(
        NOT(ISBLANK([Selected Period Net Price Per Unit])), min_value)

However, I want productwise Min and Max so I am struggling to find out the solution.

[VahidDM]

Hi @_ajinkyaj 

Try using ALLEXCEPT to preserve the product context while removing other filters. For example, for the minimum price per product:

Min NPPU Per Product =
CALCULATE(
    MINX(
        ADDCOLUMNS(
            SUMMARIZE('FactTable', 'Product'[Product Name], 'ShipTo'[Ship To Name], 'Date'[Month-Year]),
            "@NPPU", [Selected Period Net Price Per Unit]
        ),
        [@NPPU]
    ),
    ALLEXCEPT('Product', 'Product'[Product Name]),
    ALLSELECTED()
)

This ensures the measure returns the minimum Net Price Per Unit within the current product's context, ignoring other filters. Do a similar measure for the maximum value by replacing MINX with MAXX.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[_ajinkyaj]

@VahidDM ,
Thanks for replying, However, the dax you mentioned did not behave what I was expecting. It does not even return the global min of that matrix. 

[_ajinkyaj]

@lbendlin , @VahidDM I want to calculate the variance of Net Price Per Unit for each product. For a selected period, there could be no number of STs(Ship To) who have sold that product. I want to see; is there variation in the Net Price Per Unit over those many STs?
As I mentioned, I am able to calculate globle min and max over all the Products and Ship To's within that period using above mentioned DAX like in case of Min below I am getting 

Month-Year/Product Name	Ship To Name	Jan-2024	Feb-2024	Apr-2024
Product A	ST 1	$54.92		$54.92
Product A	ST 2	$54.92	$54.92
Product B	ST 4	$54.92		$54.92
Product B	ST 5	$54.92
Product C	ST 4	$54.92	$54.92	$54.92

.
However, I want it Min as

Month-Year/Product Name	Ship To Name	Jan-2024	Feb-2024	Apr-2024
Product A	ST 1	$54.92		$54.92
Product A	ST 2	$54.92	$54.92
Product B	ST 4	$56.70		$56.70
Product B	ST 5	$56.70
Product C	ST 4	$57.70	$57.70	$57.70

[Anonymous]

Hi @_ajinkyaj 

Thank you very much lbendlin and VahidDM for your prompt reply.

Please try to get Min using the following code:

Min or Max Product-ST = 
var min_value = 
CALCULATE(
        MIN('Table'[Net Price Per Unit]),
        ALLEXCEPT('Table', 'Table'[Product Name])
)
RETURN
IF(
    NOT(ISBLANK(SELECTEDVALUE('Table'[Net Price Per Unit]))),
    min_value
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?