How to sum only certain value to put in on any visuals

[damit230183]

Hello,

I have this table,

I would like to create CARD for total which should show 5900 not 41,300.

In this image generally every friday we send out some email to certain users which you can see one week it was 2900 and next week it's 3000, Therefore, when i add those it should be 5900 total send out email for two weeks.

Any suggestion or idea how can i achieve this?

Thanks

[Idrissshatila · Accepted solution]

Hello @damit230183 ,

you can try the follwoing measure, and in this measure you'll only sum one day per week which will result as you mentioned

Total Fridays = 
CALCULATE(
    SUM(YourTable[Total]),
    WEEKDAY(YourTable[Date], 2) = 5  
)

[MasonMA · Accepted solution]

Hi,

If you do not have a Date table filtering this table in your post, i'd suggest first creating one. 

Add a 'WeekStart'column in date table as below. 

and use below Measure for your Card. 

Emails =

SUMX (

    ADDCOLUMNS (

        VALUES ( DateTable[WeekStart] ),

        "@WeeklyTotal", CALCULATE ( MAX ( Card[DailyTotal] ) )

    ),

    [@WeeklyTotal]

)

[Ashish_Mathur]

Hi,

Create a Calendar table and a relationship (Many to One and Single) from the Date column of your Table to the Date column of the Calendar table.  In the Calendar Table. create this calculated column formula

DoW = format(calendar[date],"dddd")

Write this measure

Measure = calculate(sum(Data[Total]),Calendar[DoW]="Friday")

Hope this helps.

[damit230183]

Thank you all for your quick responses. All solution worked for me as expected.

Now I have follow up question on same table....

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

[krishnakanth240 · Accepted solution]

Hi @damit230183 

Can you check this measure

[MasonMA · Accepted solution]

Hi,

If you do not have a Date table filtering this table in your post, i'd suggest first creating one. 

Add a 'WeekStart'column in date table as below. 

and use below Measure for your Card. 

Emails =

SUMX (

    ADDCOLUMNS (

        VALUES ( DateTable[WeekStart] ),

        "@WeeklyTotal", CALCULATE ( MAX ( Card[DailyTotal] ) )

    ),

    [@WeeklyTotal]

)

[Idrissshatila · Accepted solution]

Hello @damit230183 ,

you can try the follwoing measure, and in this measure you'll only sum one day per week which will result as you mentioned

Total Fridays = 
CALCULATE(
    SUM(YourTable[Total]),
    WEEKDAY(YourTable[Date], 2) = 5  
)