PRICE RANGE WITH DIFFERENT PARAMETER

[Anonymous]

Hi Guys,

I need help. We sell multiple item with different category. I need a DAX that will determine or create report base on different category with different  price range base. Example

CATEGORY    CATEGORY 2     PRICE  QTY SOLD
APPLE              FRUITS             300           4
BANANA          FRUITS            450            2
GRAPES           FRUITS             1000         7
DOG                ANIMALS         15000         8
CAT                 ANIMALS          700            2
GOAT              ANIMALS           5000         1
DRESS             CLOTHES           250           1
PANTS             CLOTHES           50            7
GOWN            CLOTHES         10000        10

SHEET 2

CATEGORY        ENTRY      MID                HIGH
FRUITS               0-300      301-999          above 1000
ANIMALS          0-5000      5001-10000    10001 above
CLOTHES           0-500         501-5000        5001 above

i want get total sold qty per category per price range. Hope you can help me.

thank you in advance

[v-yulgu-msft]

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

[Anonymous]

Hi @v-yulgu-msft,

Good Afternoon,

Can you help about my problem?

Anyone who can help me?

Thank you,

Eanne

[Anonymous]

Hi GUys

Any help?

[Anonymous]

Hi @v-yulgu-msft,

Can you help me with regards to my new given data?

thank you,

Eanne

[Anonymous]

Hi Sir,

Thank you for immediate response. I forgot to incude that the 2 file have 1 connecting file. The masterfile (relationship)

FIRST FILE (MASTERFILE)

FILE 2 (with relationship in file 1 using item column)

FILE 3

already change the arrangement from previous file to lessen steps

now need a DAX that will determine or create report base on different category with different  price range base.  using this 3 file

Category will be the basis of price range. 

i want get total sold qty per category per price range. Hope you can help me.

thank you so much in advance,