Number of Order on each segment

DAX DAX Calculations
1

Dear Dax Experts
I did order segmentation to know how many customers mebmer of each segment, please check the below screen-shot:

image
image1133×554 7.2 KB

Each segment displays how many customers belongs to each order segment in the tooltip , I need also to display how many orders for each segment on the card e.g.
Total Orders = 74K
Segment 1 to 5 = 50K Order
Segment 6 to 10 = 20K Order
Segment +11 = 4K Order

Also I want to create (Table custom visual) to display the transactions belongs
to each segment if the user click the segment bar.

I attached the resource with this post.
Thank youCustomers Dynamic Segmentation by Order Count.pbix (649.8 KB)

2

Hi @MAAbdullah47, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

@MAAbdullah47 This measure should get you what you are looking for:

Number of Orders per Segment =
VAR CustomersByOrders =
    SUMMARIZE (
        ORders,
        Orders[Customer ID],
        "Orders", DISTINCTCOUNT ( Orders[Order ID] )
    )
VAR Customers1to5 =
    SUMMARIZE (
        FILTER ( CustomersByOrders, [Orders] >= 1 && [Orders] <= 5 ),
        [Customer ID]
    )
VAR Customers6to10 =
    SUMMARIZE (
        FILTER ( CustomersByOrders, [Orders] >= 6 && [Orders] <= 10 ),
        [Customer ID]
    )
VAR Customers11plus =
    SUMMARIZE ( FILTER ( CustomersByOrders, [Orders] >= 11 ), [Customer ID] )
RETURN
    SWITCH (
        SELECTEDVALUE ( Segment[Segment Name] ),
        "1 to 5",
            CALCULATE (
                DISTINCTCOUNT ( Orders[Order ID] ),
                FILTER ( Orders, Orders[Customer ID] IN Customers1to5 )
            ),
        "6 to 10",
            CALCULATE (
                DISTINCTCOUNT ( Orders[Order ID] ),
                FILTER ( Orders, Orders[Customer ID] IN Customers6to10 )
            ),
        "11+",
            CALCULATE (
                DISTINCTCOUNT ( Orders[Order ID] ),
                FILTER ( Orders, Orders[Customer ID] IN Customers11plus )
            ),
        0
    )

image

I wasn’t sure what the goal was with the [order count] but you could change out the DISTINCTCOUNT with the SUM(Orders[order count]) in the switch statement too.

image

2 Likes
4

Thank you @datazoe it works.

1 Like
5

Hi @MAAbdullah47, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!