Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Hi @v-yulgu-msft,
Good Afternoon,
Can you help about my problem?
Anyone who can help me?
Thank you,
Eanne
Hi GUys
Any help?
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
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,
User | Count |
---|---|
106 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |