PRICE RANGE WITH DIFFERENT PARAMETER

Question

Hi Guys,&nbsp;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&nbsp; price range base. Example&nbsp;&nbsp;CATEGORY&nbsp;&nbsp;&nbsp; CATEGORY 2&nbsp;&nbsp;&nbsp;&nbsp; PRICE&nbsp; QTY SOLDAPPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FRUITS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4BANANA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FRUITS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 450&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2GRAPES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FRUITS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7DOG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ANIMALS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8CAT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ANIMALS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 700&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2GOAT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ANIMALS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1DRESS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLOTHES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1PANTS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLOTHES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7GOWN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CLOTHES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;SHEET 2&nbsp;CATEGORY &nbsp; &nbsp; &nbsp;&nbsp; ENTRY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; HIGHFRUITS &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0-300 &nbsp; &nbsp;&nbsp; 301-999 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; above 1000ANIMALS &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 0-5000 &nbsp; &nbsp;&nbsp; 5001-10000 &nbsp;&nbsp; 10001 aboveCLOTHES &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0-500 &nbsp; &nbsp; &nbsp; &nbsp; 501-5000 &nbsp; &nbsp; &nbsp;&nbsp; 5001 above&nbsp;&nbsp;i want get&nbsp;total sold qty per category per price range. Hope you can help me.&nbsp;thank you in advance&nbsp;&nbsp;

v-yulgu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
Please apply below changes to Sheet2 in Query Editor mode.
&nbsp;
Unpivot table.

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

&nbsp;
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] &lt;&gt; BLANK ()
            &amp;&amp; [PRICE] &gt;= [Min]
            &amp;&amp; [PRICE] &lt;= [Max],
        [QTY SOLD],
        IF ( [Max] = BLANK () &amp;&amp; [PRICE] &gt;= [Min], [QTY SOLD], 0 )
    ),
    "Value Range", IF ( [Max] &lt;&gt; BLANK (), [Min] &amp; "-" &amp; [Max], "above " &amp; [Min] )
)

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

&nbsp;
Best regards,
Yuliana Gu

anonymous · Answer

Hi Sir,&nbsp;Thank you for immediate response. I forgot to incude that the 2 file have 1 connecting file. The masterfile (relationship)&nbsp;FIRST FILE (MASTERFILE)&nbsp;FILE 2 (with relationship in file 1 using item column)&nbsp;&nbsp;&nbsp;&nbsp;FILE 3&nbsp;already change the arrangement from previous file to lessen steps&nbsp;now need a DAX that will determine or create report base on different category with different&nbsp; price range base.&nbsp; using this 3 file&nbsp;Category will be the basis of price range.&nbsp;i want get&nbsp;total sold qty per category per price range. Hope you can help me.&nbsp;&nbsp;thank you so much in advance,&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;v-yulgu-msft,&nbsp;Can you help me with regards to my new given data?&nbsp;thank you,&nbsp;Eanne

anonymous · Answer

Hi v-yulgu-msft,&nbsp;Good Afternoon,&nbsp;Can you help about my problem?&nbsp;Anyone who can help me?&nbsp;&nbsp;Thank you,&nbsp;Eanne

anonymous · Answer

Hi GUys&nbsp;Any help?

Forum Discussion

PRICE RANGE WITH DIFFERENT PARAMETER

5 Replies

Related content

PBIRS Deployment Pricing

Reserved Fabric Pricing

Pricing in details

Parameter in summarize

Pricing calculator