Forum Discussion

powerbidu's avatar
powerbidu
Frequent Visitor
1 year ago
Solved

write measure/dax to calculate min max and % difference

DateIdentifierOrder NumberPrice
1-OctA1231.15
1-OctA1231.15
1-OctA1241.00
1-OctA1251.20
1-OctB1262.00
1-OctB1262.20
1-OctC1273.10
1-OctC1283.00
1-OctC1293.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.

 
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. 

4 Replies

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    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()
    )

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    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. 

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    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] )
  • 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.