PRICE RANGE WITH DIFFERENT PARAMETER

Hi Anonymous,

Please apply below changes to Sheet2 in Query Editor mode.

Unpivot table.

Right click the [Value] column and select "Replace values". Then, split column.

In data view mode. Create calculated tables like below.

Sheet3 =
ADDCOLUMNS (
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                Sheet1,
                "CATEGORY2", Sheet1[CATEGORY2],
                "PRICE", Sheet1[PRICE],
                "QTY SOLD", Sheet1[QTY SOLD]
            ),
            Sheet2
        ),
        [CATEGORY] = [CATEGORY2]
    ),
    "QTY", IF (
        [Max] <> BLANK ()
            && [PRICE] >= [Min]
            && [PRICE] <= [Max],
        [QTY SOLD],
        IF ( [Max] = BLANK () && [PRICE] >= [Min], [QTY SOLD], 0 )
    ),
    "Value Range", IF ( [Max] <> BLANK (), [Min] & "-" & [Max], "above " & [Min] )
)


Sheet4 =
SUMMARIZE (
    Sheet3,
    Sheet3[CATEGORY2],
    Sheet3[Range],
    Sheet3[Value Range],
    "Total QTY", SUM ( Sheet3[QTY] )
)

Best regards,

Yuliana Gu