Summarize by 2 columns

SammyNed — Mon, 18 Oct 2021 19:34:35 GMT

Hi guys,

Need some assistance please.

This is long winded, but I have the following table, simplified below. Basically customer ID's, date of order and order category. I want to know how many cummulative 'Loyal" customers per month and what category the LOYALS are buying from per month. a 'Loyal" customer is someone who has ordered more than once.

I want to know how many loyal customers i accumulate per month as well as which category they are ordering from.

So in March we should only have 1 loyal customer (customer A with 3 orders).

In April we should have 2 loyal customers (customer A with a total of 4 orders and customer C with a total of 3 orders). This ties in with the first graph and DAX measure below

Then secondly of those that are loyal, i want to know how many orders they place per category per month, as per the second graph.

So in Mar, A ( who is loyal now) spent 1 order in 3 categorys.

In April, A (loyal for life) ordered in 1 category and C (who is now Loyal) ordered from 2 different categorys.

I have the following thus far to make the first graph, but can't seem to incorporate the category type to get the second graph as it then starts calculating the loyal customers PER category which isn't correct.

Loyal Customers Count =

var _month =

MAX(Date[Date])

var _untilcurrentmonthyeartable =

FILTER(ALL(Sales),Date<= _month)

var _customercounttable =

GROUPBY( _untilcurrentmonthyeartable, ID, "@customercount", SUMX(CURRENTGROUP(),1))

var _loyalcustomerstable =

FILTER(_customercounttable,[@customercount]>=2)

Return

COUNTROWS(_loyalcustomerstable)

Please help.

Thanks in advance

SammyNed

Re: Summarize by 2 columns

amitchandak — Tue, 19 Oct 2021 03:13:03 GMT

SammyNed , You can use date and month year from date table, if you are using that in visual

Try measure like

countx(filter(summarize(Table, Table[Customer],"_1", calculate(distinctcount(Table[Date]))),[_1] >1),[Customer])

countx(filter(summarize(Table, Table[Customer], Table[Month Year],"_1", calculate(distinctcount(Table[Date]))),[_1] >1),[Customer])

Re: Summarize by 2 columns

SammyNed — Wed, 20 Oct 2021 12:20:36 GMT

amitchandak

Thank you for your reply, but i don't see how your solution can give me the second graph which needs to include the categorys purchased? Am i missing something?

topic Re: Summarize by 2 columns in DAX Commands and Tips

Summarize by 2 columns

Re: Summarize by 2 columns

Re: Summarize by 2 columns