Forum Discussion
Summarize + All(single Column)?
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!
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
8 Replies
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 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.
please provide data in text format.
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:
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 attachedThank 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?
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!!
