summarizing data in measure with date dynamically

[KDew2020]

Hello,

I am working on creating measure to identify the customers who have bought products in retail or digital or both retail and digital daily, weekly or yearly dynamically.

below is the same data I have. I have tried to use summarize and group by function but unable to get the measure created.

datetimeSales	CustomerID	Channel	Product	Sales
01-Oct-20	1001	Online	abc	20
01-Oct-20	1001	Retail	xyz	10
02-Oct-20	1002	Retail	abc	5
03-Oct-20	1003	Online	xyz	40
04-Oct-20	1003	Online	abc	20
05-Oct-20	1004	Online	xyz	10
06-Oct-20	1005	Retail	abc	5

[v-easonf-msft]

Hi, @KDew2020 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[negi007]

@KDew2020 If you do not want to complicate your data structure there is a quick fix solution for you. You create matrix visual and add the subtotal in this. Subtotal name can be changed to anything from the matrix visual settings.

Let me know if above solution works for you.

Else second solution is to create a duplicate table and then group channel values to a new channel "Both" in that channel and then combine these two tables into one so that you can have all channels and also Both channel in the same table.

[KDew2020]

thank you for the response but I am looking for 3 customer type

1. who bought only in retail

2. who bought only in online

3. who bought in both retail and online

the result for daily should be like below:

datetimeSales	CustomerType	Total Sales
01-Oct-20	Both	30
02-Oct-20	Retail	5
03-Oct-20	Online	40
04-Oct-20	Online	20
05-Oct-20	Online	10
06-Oct-20	Retail	5

similar result should be for week/Month/year where datetimeSales will be replaced with week number/Month number and year.

[negi007]

@KDew2020 You can create below measure to view sales by channel. 

SaleByChannel = CALCULATE(SUM('GroupBy Sales'[Sales]),GROUPBY('GroupBy Sales','GroupBy Sales'[Channel]))

[amitchandak]

@KDew2020 , I do not saw digital as channel, But this will one or both

create a new measure

calculate(sum(Table[sales]), filter(Table, Table[channel] in {"Retail", "Digital"}))

plot it with customer