Measure with biggest and smallest value

[qwertuiop2267]

Hi, I have a data set with a sales list. I want to create a measure that will return the names of products with the largest and smallest number of units sold at the same time. If a given month is selected in the visualization, I want the measure to check the largest and smallest number of items sold from that month.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file if it suits your requirement.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Expected result measure: =
VAR _productwithqty =
    ADDCOLUMNS (
        VALUES ( 'Product'[Product] ),
        "@qty", CALCULATE ( SUM ( Sales[Quantity sold] ) )
    )
VAR _toptable =
    WINDOW ( 1, ABS, 1, ABS, _productwithqty, ORDERBY ( [@qty], DESC ) )
VAR _bottomtable =
    WINDOW ( 1, ABS, 1, ABS, _productwithqty, ORDERBY ( [@qty], ASC ) )
VAR _topqtyproduct =
    CALCULATE ( MAX ( 'Product'[Product] ), _toptable )
VAR _topqty =
    CALCULATE ( SUM ( Sales[Quantity sold] ), _toptable )
VAR _bottomqtyproduct =
    CALCULATE ( MAX ( 'Product'[Product] ), _bottomtable )
VAR _bottomqty =
    CALCULATE ( SUM ( Sales[Quantity sold] ), _bottomtable )
RETURN
    "Top: " & _topqtyproduct & " | " & _topqty & " / Bottom: " & _bottomqtyproduct & " | " & _bottomqty