Forum Discussion

Abrar786's avatar
Abrar786
Icon for Helper I rankHelper I
3 years ago
Solved

Customer increase sale with Dynamic Segmentation

 


I want to categorize the "Increase Sale" column into the following categories, but the total for "Increase Sale" is not displaying correctly with the category name. If you look at the table above, Customer_no CU323143 has an Increase Sale of $155,369.31, so it should be displayed under the category of $100k-250k.

 

 

Following are my measures:

Increase Sale =

CALCULATE (

    [CY Sale] - [LY Sale],

    FILTER (

        VALUES ( invoices_details[customer_no] ),

        [CY Sale] > 0

            && [LY Sale] > 0

            && [CY Sale] > [LY Sale]

    )

)

 

WHERE

CY Sale =

CALCULATE (

    SUM ( invoices_details[sale_cad] ),

    FILTER (

        VALUES ( invoices_details[customer_no] ),

        COUNTROWS (

            FILTER (

                'category',

                invoices_details[sale_cad] >= 'category'[category min value]

                    && invoices_details[sale_cad] < 'category'[category max value]

            )

        ) > 0

    )

)

 

LY Sale =

CALCULATE([CY Sale],SAMEPERIODLASTYEAR('Calendar'[Date]))

 

 

  • Abrar786 , Try like

     

    Increase Sale =
    Sumx (FILTER (
    VALUES ( invoices_details[customer_no] ),
    [CY Sale] > 0
    && [LY Sale] > 0
    && [CY Sale] > [LY Sale]
    ),
    [CY Sale] - [LY Sale]
    )

     

    or

     

    Sumx (FILTER (
    VALUES ( invoices_details[customer_no] ),
    [CY Sale] > 0
    && [LY Sale] > 0
    && [CY Sale] > [LY Sale]
    ),
    calculate([CY Sale] - [LY Sale])
    )

2 Replies

  • Abrar786 , Try like

     

    Increase Sale =
    Sumx (FILTER (
    VALUES ( invoices_details[customer_no] ),
    [CY Sale] > 0
    && [LY Sale] > 0
    && [CY Sale] > [LY Sale]
    ),
    [CY Sale] - [LY Sale]
    )

     

    or

     

    Sumx (FILTER (
    VALUES ( invoices_details[customer_no] ),
    [CY Sale] > 0
    && [LY Sale] > 0
    && [CY Sale] > [LY Sale]
    ),
    calculate([CY Sale] - [LY Sale])
    )

  • Hi amitchandak ,

    With my measure 

    Increase Sale =

    CALCULATE (

        [CY Sale] - [LY Sale],

        FILTER (

            VALUES ( invoices_details[customer_no] ),

            [CY Sale] > 0

                && [LY Sale] > 0

                && [CY Sale] > [LY Sale]

        )

    )

    I am obtaining the correct result for each customer, but I am unable to place the sum of the increase in sales under the respective category name.

     

    Thank you.