How to sum total email read by group

[damit230183]

Hi, 

Need help in to calculate sum of Total read by group    (Total send by week).

In this case, every Friday email send out to 3000 users once then need to track how many users have read that email. Please refer table below.

Table is about email send to how many users weekly which 3000....now I want to calculate about how many total users have read that email as seen in new image below. Some day there will be more someday there will be less i just want add difference as seen image below. Total read should not be more than 3000.

In this case for 1 week total users who got email are 3000, but till the week finish only 2950 has read that email.

Any suggestion or advice ?

Thanks

[danextian · Accepted solution]

Hi @damit230183 

Your requirements are somewhat unclear. Assuming you want to sum the total reads once per week and that the weekly read count always increases, create a week indicator column and define the following measures.

Week Number (From First Friday) = 
VAR FirstFriday =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            WEEKDAY ( 'Table'[Date], 2 ) = 5   -- Friday (Mon=1)
        )
    )
RETURN
INT ( ( 'Table'[Date] - FirstFriday ) / 7 ) + 1

Total Sent = 
SUMX (
    VALUES ( 'Table'[Week Number (From First Friday)] ),
    CALCULATE ( MAX ( 'Table'[Total] ) )
)

Total Read = 
SUMX (
    VALUES ( 'Table'[Week Number (From First Friday)] ),
    CALCULATE ( MAX ( 'Table'[Read] ) )
)

Please see the attached pbix.

[ryan_mayu]

@damit230183 

I think you posted two similar questions. pls see if the answer in that post is helpful.

Re: Need help in to sum difference only based on w... - Microsoft Fabric Community

[danextian · Accepted solution]

Hi @damit230183 

Your requirements are somewhat unclear. Assuming you want to sum the total reads once per week and that the weekly read count always increases, create a week indicator column and define the following measures.

Week Number (From First Friday) = 
VAR FirstFriday =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            WEEKDAY ( 'Table'[Date], 2 ) = 5   -- Friday (Mon=1)
        )
    )
RETURN
INT ( ( 'Table'[Date] - FirstFriday ) / 7 ) + 1

Total Sent = 
SUMX (
    VALUES ( 'Table'[Week Number (From First Friday)] ),
    CALCULATE ( MAX ( 'Table'[Total] ) )
)

Total Read = 
SUMX (
    VALUES ( 'Table'[Week Number (From First Friday)] ),
    CALCULATE ( MAX ( 'Table'[Read] ) )
)

Please see the attached pbix.

[v-dineshya]

Hi @damit230183 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @danextian , Thank you for your prompt response.

Hi @damit230183 could you please try the proposed solution shared by @danextian  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @damit230183 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[krishnakanth240]

Hi @damit230183 

Are you looking to calculate the Difference column as output?

[damit230183]

Thanks for your response. 

Yes I am looking for difference between rows then whatever number we get from difference should be add (Sum).

[Ashish_Mathur]

Hi,

Just cannot understand your requirement.  Share the input data in a format that can be pasted in an MS Excel file.  Show the expected result.

[damit230183]

Sure, first of all Thanks for responding.

So I have table in this format 

Whereas, you can see I can total weekly email sent to 3000 users for 1st week and for 2nd week total email sent to 2900 users. This total should 5900. 

Now, If you look at READ column, it shows how many reader read that email on daily basis,

For example, first day for 2nd week total reader read email is 2800....then every day some more people read so number add to 2800. Total number should not be more than 2900 because only those users have received that email.

What I want is to have Difference column as shown in image below

Once, I have difference column, I would like to add that column so I know total email sent to 5900 users in two weeks, whereas total 5840 total users have read out of 5900.

Hope this make sense more.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[cengizhanarslan]

Please try the formula below:

Read =
SUM ( Emails[Read] )   -- or MAX, depending on your grain

Difference =
VAR TodayRead = [Read]
VAR PrevRead  = CALCULATE ( [Read], DATEADD ( 'Calendar'[Date], -1, DAY ) )
RETURN
TodayRead - PrevRead

In description you say weeklt but the table is daily, if it is truly weekly you could change -1 to -7 in the formula.