DAX grouping by a measure result

Question

Need some help here grouping a measure result for a visual in PowerBI using DAX. I have a table that displays all visits by certain customers (Visits table). I have a measure that counts the number of visits for each day, and I have a date slicer (visit day). The results of this measure usually range between 1 and 10. I am trying to see a frequency of customers visits per year. If they have

1 = single visit
3 or more visits = returning customer
7 or more visits = frequent customer.

I am trying to use the pie chart/ donut chart to display the breakdown of my customers within a year. I believe this is possible, but I am missing something. Any help would be appreciated!

Customer_id          Visit_date      Purchase_total

The above is a sample of the visits table. There is also a measure that uses DAX to get the Total Visits Per Day. (Some customers come in multiple times during a given day [visits_day].)

Can you provide a sample of what your visits table looks like? — Alexis Olson
customer_ID Visit_Date purchase_total These are the column headers. the measure visits_day is also included using DAX — wood47

Alexis Olson Alexis Olson · Accepted Answer · 2018-09-04T16:29:57

Grouping by a measure in a pie chart is a bit tricky since you can't put a measure into e.g. the Legend box and you can't use a calculated column instead if you want the grouping to change based on your slicer selections.

Here's one possible workaround:

First, create a new Buckets table for your category buckets.

Bucket
------
single visit
returning customer
frequent customer

This Buckets[Bucket] column is what you'll use in your Legend section.

For the Values section, we need a new measure:

Count of Visits =
VAR Summary =
    SUMMARIZE (
        Visits,
        Visits[Customer_id],
        "Bucket", SWITCH (
            TRUE (),
            COUNTROWS ( Visits ) >= 7, "frequent customer",
            COUNTROWS ( Visits ) >= 2, "returning customer",
            COUNTROWS ( Visits ) = 1, "single visit"
        )
    )
RETURN
    SUMX ( Summary, IF ( [Bucket] = SELECTEDVALUE ( Bucket[Bucket] ), 1, 0 ) )

This measure summarizes the current Visits table (with any slicer filtering applied) by putting each customer into one of the three buckets based on the number of times they've visited in your selected date range. Then we count how many customers fall into the current pie chart section by adding 1 only for the customers in the summary table whose bucket matches the current pie chart bucket (and 0 otherwise).

Note: This measure categorized your customers for the dates you have selected. If you don't care about that and only need to calculate their category for fixed period(s) (i.e. you don't want your date slicer to change the bucket they belong to), then you can just use a calculated column instead of needing to create a new table.

DAX grouping by a measure result

1 Answers