How to change the range of sum

[MostafaGamal]

Hi All, 

I use the following measure to calclate the No. of Entries for each user and it works fine, but i want the measure to calculate the values in the first 2 rows for each MailOfUser ( this is a column in the table we use )  

Please support how to do this 

[v-yingjl]

Hi @MostafaGamal ,

To get the expected result, need a new column as a row mark of each mailuser.

1. Add an index column manually in power query first:

2. Create a new calculated column [Count] as a row mark of each mailuser:

Count =
CALCULATE (
    COUNT ( BOSTrcaking[Department] ),
    FILTER (
        ALLEXCEPT ( BOSTrcaking, BOSTrcaking[Department] ),
        'BOSTrcaking'[Index] <= EARLIER ( BOSTrcaking[Index] )
    )
)

3. Modify your previous measure like this:

Sum =
CALCULATE ( SUM ( BOSTrcaking[EntriesCount] ), 'BOSTrcaking'[Count] <= 2 )

You will get the expected result:

Attached my sample file that hopes to help you, please check and try it: How to change the range of sum.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MostafaGamal]

@v-yingjl  i followed the steps you give to me but it gives me this error, please advise 

[v-yingjl]

Hi @MostafaGamal ,

After creating an index column, you can create a measure directly to try to optimize memory to get the same result:

Re =
VAR tab =
    SUMMARIZE (
        BOSTrcaking,
        BOSTrcaking[Department],
        BOSTrcaking[Index],
        BOSTrcaking[EntriesCount],
        "count", CALCULATE (
            COUNT ( BOSTrcaking[Department] ),
            FILTER (
                ALLEXCEPT ( BOSTrcaking, BOSTrcaking[Department] ),
                'BOSTrcaking'[Index] <= MAX ( 'BOSTrcaking'[Index] )
            )
        )
    )
RETURN
    SUMX ( FILTER ( tab, [count] <= 2 ), [EntriesCount] )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MostafaGamal]

@v-yingjl thanks for youe support, the formula you create gets the result without need to create count column 

but the vaues i get is not what i want, calculate 2 entries for the HSE while it should be 3 entries since one user create 3 entries and another user in the same department HSE create 1 enty that 3 not 2 ( the target is 2 entries so i want to count the first 2 rows for a certain period of time to avoid calculating other users who create more than 2 entries ) 

[v-yingjl]

Hi @MostafaGamal ,

If I got it correctly, modify the measure like this:

Re = 
VAR tab =
    SUMMARIZE (
        BOSTrcaking,
        BOSTrcaking[Department],
        BOSTrcaking[Index],
        BOSTrcaking[EntriesCount],
        "count", CALCULATE (
            COUNT ( BOSTrcaking[Department] ),
            FILTER (
                ALLEXCEPT ( BOSTrcaking, BOSTrcaking[Department], BOSTrcaking[MailOFUser] ),
                'BOSTrcaking'[Index] <= MAX ( 'BOSTrcaking'[Index] )
            )
        )
    )
RETURN
    SUMX ( FILTER ( tab, [count] <= 2 ), [EntriesCount] )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MostafaGamal]

@v-yingjl thanks for your response, i have only on last problem that the " EntriesTime " which is date is not affecting the formula 

i want it to count the first 2 rows for every user in the date rane i choose 

[v-yingjl]

Hi @MostafaGamal ,

You can create a slicer and put the date field in it to achieve this.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MostafaGamal]

@v-yingjl  

@v-yingjl  

I already did that but when I change the silcer date it gives blank 

The dates in my data starts from 1 July till 31 August 

The formula works fine in July but when I choose auguest in the slicer it gives me blank as there's no values To be counted in this range of time 

[v-yingjl]

Hi @MostafaGamal ,

Since there is no data in August, how could it show the result? 0 or others?

Could you give me an example and expected output based on my sample table so that I could better help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[MostafaGamal]

@v-yingjl 

there is a values in august but the formula didt see it, here is the table of data i use (below) , you can see that there is values in the clolumn " EntriesCount " 

thats what i expect from the measure a table that count the entries for every user in the first 2 rows 

but when i choose the slicer in august it gives me blank 

Index	Department	Line	Name	Mail	EntriesCount	EntriesTime
1	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/1/2020
3	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	7/14/2020
4	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	7/15/2020
5	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	7/16/2020
6	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	7/17/2020
7	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	1	7/13/2020
8	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	7/16/2020
9	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	7/17/2020
10	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	7/18/2020
11	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	7/12/2020
12	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	7/13/2020
13	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	7/14/2020
14	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	7/15/2020
16	FC		Hesham Barakat	barakat.hs@pg.com	1	7/13/2020
17	FC		Hesham Barakat	barakat.hs@pg.com	1	7/17/2020
18	FC		Hesham Barakat	barakat.hs@pg.com	0	7/16/2020
19	FC		Hesham Barakat	barakat.hs@pg.com	0	7/22/2020
20	FC		Sameh Mohammedy	sameh.ms@pg.com	1	7/12/2020
21	FC		Sameh Mohammedy	sameh.ms@pg.com	0	7/13/2020
22	FC		Sameh Mohammedy	sameh.ms@pg.com	0	7/31/2020
23	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	8/1/2020
25	HSE		Mostafa Gamal	metwaly.mg@pg.com	1	8/14/2020
26	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	8/15/2020
27	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	8/16/2020
28	HSE		Mostafa Gamal	metwaly.mg@pg.com	0	8/17/2020
29	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	1	8/13/2020
30	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	1	8/16/2020
31	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	8/17/2020
32	HSE		Abdullrahman Barakat	ali.ab.1@pg.com	0	8/18/2020
33	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	7/12/2020
34	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	1	8/13/2020
35	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	8/14/2020
36	HSE		Nour Abdelaziz	abdelaziz.n@pg.com	0	8/15/2020
37	FC		Hesham Barakat	barakat.hs@pg.com	1	8/1/2020
38	FC		Hesham Barakat	barakat.hs@pg.com	1	8/13/2020
40	FC		Hesham Barakat	barakat.hs@pg.com	0	8/16/2020
41	FC		Hesham Barakat	barakat.hs@pg.com	0	8/22/2020
42	FC		Sameh Mohammedy	sameh.ms@pg.com	1	8/12/2020
43	FC		Sameh Mohammedy	sameh.ms@pg.com	0	8/13/2020
44	FC		Sameh Mohammedy	sameh.ms@pg.com	0	8/31/2020

[v-yingjl · Accepted solution]

Hi @MostafaGamal ,

Modify the measure like this:

Re = 
VAR tab =
    SUMMARIZE (
        BOSTrcaking,
        BOSTrcaking[Department],
        BOSTrcaking[Index],
        BOSTrcaking[EntriesCount],
        "count", CALCULATE (
            COUNT ( BOSTrcaking[Department] ),
            FILTER (
                ALL ( BOSTrcaking ),
                'BOSTrcaking'[Department] IN DISTINCT ( 'BOSTrcaking'[Department] )
                    && 'BOSTrcaking'[EntriesTime].[Month]
                        IN DISTINCT ( 'BOSTrcaking'[EntriesTime].[Month] )
                    && 'BOSTrcaking'[MailOFUser] IN DISTINCT ( BOSTrcaking[MailOFUser] )
                    && 'BOSTrcaking'[Index] <= MAX ( 'BOSTrcaking'[Index] )
            )
        )
    )
RETURN
    SUMX ( FILTER ( tab, [count] <= 2 ), [EntriesCount] ) + 0

Result:

Attached sample file, please check it: Sample.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @MostafaGamal ,

Please try to release your pc memory or filter the data table without unnecessary data.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@MostafaGamal what identifies the first two-lines, it has to be business logic, not plain English.

[MostafaGamal]

@parry2k  thanks for your reply, But i dont understand what you mean by not plain english ? 

[parry2k]

@MostafaGamal you mentioned the first two lines, doesn't mean anything until you provide the business logic. I hope it clarifies.

[MostafaGamal]

@parry2k  Every row have a uniqe identefires which is the ( Mail of user ) and since every user have almost like 60 rows i would like to sum the values in the first 2 rows for every user not the sum of all 60 rows 

I hope that i made myself clear ? 

[parry2k]

@MostafaGamal ok , again what is first tow rows, sorted by date, some id  or what?

[MostafaGamal]

@parry2k  they will be sorted by the mail of the user as its s special for every user in this database