Forum Discussion

powerbidu's avatar
powerbidu
Frequent Visitor
1 year ago
Solved

write measure/dax to calculate min max and % difference

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....
Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    1 year ago

    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. 

v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

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.