Summarize + All(single Column)?

[tbones]

Hello all,

I have among others one table "Activities" with activities in different territories (see in attached screenshot).

Example is that territoy TerrA got 3 activities in January 21 over 2 days:

• overall activities per field day would be: 3/2 ->> 1,5
• if this is visualized per channel, it would be:
  • 1,0 F2F activity per field day
  • 0,5 Email activity per field day

Target: Get a stacked bar chart for activities per field day for each channel

• 1,0 F2F
• 0,5 Email
• 1,5 total

thank you for your help!

[tbones]

I found the reason.

I have a calculated column to sort the "channels" column using relationship to another channel sorting table.

after adding REMOVEFILTERS also to this "channel_sort" column it worked.

Tricky...

Thank you any way!!

[tbones]

Hi @vanessafvg ,

thanks for the reply!

I had also the activities and days already, but struggeling for the combination with channels.

Sorry for inaccurate explanation. I try to give more infos.

Activities per day and channel is what I need.

For F2F it would be this calculation for the given example (January 21, TerrA):

• 2 F2F activities
• 2 days with activities
• >> 2 F2F activities per total 2 days with activities = 2/2 = 1
• >> TerrA is having one F2F activity per active day

For email it would be this calculation for the given example:

• 1 email activity
• 2 days with activities
• >> 1 email activity per total 2 days with activities = 1/2 = 0,5
• >> TerrA is writing 0,5 emails per active day

The target is this one - this is what I need:

[vanessafvg · Accepted solution]

this should work if there are no other considerations

Activities Per day =

var NoofDays = CALCULATE(DISTINCTCOUNT(Activities[date ]),REMOVEFILTERS(Activities[channel]))

return DIVIDE(Activities[Activities], noofdays, 0)

see file attached

[tbones]

Thank you.

the attached PBIX looks good and it seems to work well.

After creating the measure in my modell using copy-paste strangely it didn't work.

it must be the part:

var NoofDays = CALCULATE(DISTINCTCOUNT(Activities[date ]),REMOVEFILTERS(Activities[channel]))

-> when I test this in separate measure it returns me for each channel the filtered (by channel) value like here:

Channel	Noofday
F2F	1
Email	1
Total	2

I would expect this:

Channel	Noofday
F2F	2
Email	2
Total	2

What could be the reason?

[vanessafvg]

it would be hard to tell without looking at your pbix, are you able to share it?  

[tbones]

Hi @vanessafvg ,

thanks for quick reply.

Sorry for the screenshot. Is it sufficient to copy+paste here the Excel data?

Please tell in case an upload is needed (and where to upload best):

Activities
date	territory	product	account	channel
1.1.21	TerrA	ProdA	AccA	F2F
1.14.21	TerrA	ProdB	AccB	Email
1.14.21	TerrA	ProdB	AccB	F2F
1.26.21	TerrB	ProdA	AccA	F2F
2.2.21	TerrA	ProdA	AccA	F2F
2.3.21	TerrA	ProdB	AccB	Email
Example:
Territory	TerrA
Year Month	2021 Jan
Field Days:	2
Activities:	3
Activities per Field Day
	F2F	Email
	1	0,5

[vanessafvg]

see attached but its incomplete  and not sure what other requirements it needs.

What is the definition of the values per day under email 0,5?  

see file attached to start.

[vanessafvg]

please provide data in text format.