Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
84 | |
84 | |
67 | |
63 | |
62 |
User | Count |
---|---|
208 | |
121 | |
112 | |
79 | |
70 |