write measure/dax to calculate min max and % difference

[powerbidu]

Date	Identifier	Order Number	Price
1-Oct	A	123	1.15
1-Oct	A	123	1.15
1-Oct	A	124	1.00
1-Oct	A	125	1.20
1-Oct	B	126	2.00
1-Oct	B	126	2.20
1-Oct	C	127	3.10
1-Oct	C	128	3.00
1-Oct	C	129	3.20

Hello, I have the table above. For instance, identifier A appears four times on 1st October. I would like to create formulas that display the minimum price (1.0), maximum price (1.2), and the percentage difference between the minimum and maximum price, calculated as ((1.2 - 1.0) / 1.0 = 20%).

I want these calculations to be applicable to all identifiers that have different order numbers for the same identifier (meaning I won't perform the calculations for identifier B, which has only one order number, but I would for identifier C).

Ideally, I would like to plot these values in a bar chart, with the X-axis representing the identifiers and the Y-axis showing the minimum, maximum, and percentage change, resembling trading candlestick patterns. Appreciate any advice. Thank you.

[v-ssriganesh]

Hi @powerbidu,
Thank you for reaching out to the Microsoft Fabric community forum.

I have reproduced your scenario using Power BI and created the required DAX measures to calculate the Minimum Price, Maximum Price, and Percentage Difference based on your dataset. The calculations ensure that the measures apply only when an identifier has multiple distinct Order Numbers.

Below are the DAX measures used:

Measure 1: Count Distinct Order Numbers

This measure counts the unique Order Number values per Date and Identifier.

DistinctOrderCount =

CALCULATE(

    DISTINCTCOUNT('PriceTable'[Order Number]),

    ALLEXCEPT('PriceTable', 'PriceTable'[Date], 'PriceTable'[Identifier])

)

Measure 2: Minimum Price

Returns the minimum price only if the identifier has more than one distinct order number.

MinPrice =

IF(

    [DistinctOrderCount] > 1,

    CALCULATE(

        MIN('PriceTable'[Price]),

        ALLEXCEPT('PriceTable', 'PriceTable'[Date], 'PriceTable'[Identifier])

    ),

    BLANK()

)

Measure 3: Maximum Price

Returns the maximum price under the same condition as above.

MaxPrice =

IF(

    [DistinctOrderCount] > 1,

    CALCULATE(

        MAX('PriceTable'[Price]),

        ALLEXCEPT('PriceTable', 'PriceTable'[Date], 'PriceTable'[Identifier])

    ),

    BLANK()

)

Measure 4: Percentage Difference

Calculates the percentage difference between Max and Min Price.

PercentDifference =

IF(

    [DistinctOrderCount] > 1,

    DIVIDE(

        [MaxPrice] - [MinPrice],

        [MinPrice],

        0

    ) * 100,

    BLANK()

)

I have attached a screenshot showing the expected output and a .pbix file for your reference.

Also, thanks @Deku, @rubayatyasmin, @techies for your valuable insights.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Deku]

something like this? or something else in mind?

createOrReplace
	table measures
		measure Min = MIN( 'Table'[Price] )
		measure Range = [Max] - [Min] 
		measure %Diff = DIVIDE( [Max] - [Min], [Min] )
		measure Max = MAX( 'Table'[Price] )

[rubayatyasmin · Accepted solution]

Hi, @powerbidu 

MaxPrice =
VAR OrderCount = CALCULATE(DISTINCTCOUNT(Data[Order Number]), ALLEXCEPT(Data, Data[Identifier]))
RETURN
IF(OrderCount > 1, CALCULATE(MAX(Data[Price]), ALLEXCEPT(Data, Data[Identifier])))

Same for Min, change the MAX to Min. 

PercentageChange = DIVIDE([MaxPrice] - [MinPrice], [MinPrice], 0)

Refer to the attached pbix file. 

[techies]

Hi @powerbidu please check this

Min Price =
CALCULATE(
MIN('Table'[Price]),
ALLEXCEPT('Table', 'Table'[Identifier])
)

Max Price =
CALCULATE(
MAX('Table'[Price]),
ALLEXCEPT('Table', 'Table'[Identifier])
)

% Difference =
VAR MinPrice = [Min Price]
VAR MaxPrice = [Max Price]
RETURN
IF(
MinPrice > 0,
DIVIDE(MaxPrice - MinPrice, MinPrice, 0),
BLANK()
)