cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ozgurozturkkk Frequent Visitor
Frequent Visitor

Summing of rows with values

Hi all,

 

I have two tables: Table X and Table Y.

I have one column in Table X: GBR_ENT (ALL OF THEM ARE UNIQUE)

I have two columns in Table Y: GBR_ENT(NONE OF THEM ARE UNIQUE) and Bedrag (These are the mutated amounts for the GBR_ENT).

 

So I created a relationship between table X and Table Y on GBR_ENT. Now i have created groups in Table X, by using the function "New Group" on GBR_ENT. (See the picture below)

 

pbi screen.png

 

Now i want to subtract the values of these groups like below:

 

Subtotal: (02. Kostprijs omzet - 03. Salarissen)

 

So i want to see the "bedrag" by the row "Subtotal".

 

Can you guys help me out?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Summing of rows with values

Hi @ozgurozturkkk,

 

Do you want to calculate the difference every two groups? If so, please add a calculated column in Table X first.

Group Index = RIGHT(LEFT('Table X'[GBR_ENT (groups)],2),1)

1.PNG

 

Then, create measures:

Bedrag current group =
CALCULATE (
    SUM ( 'Table Y'[Bedrag] ),
    ALLEXCEPT ( 'Table X', 'Table X'[GBR_ENT (groups)] )
)

Bedrag next group =
CALCULATE (
    SUM ( 'Table Y'[Bedrag] ),
    FILTER (
        ALLSELECTED ( 'Table X' ),
        'Table X'[Group Index]
            = SELECTEDVALUE ( 'Table X'[Group Index] ) + 1
    )
)

diff between groups = [Bedrag current group]-[Bedrag next group]

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
ibarrau Established Member
Established Member

Re: Summing of rows with values

Hi, i think you are looking for Related or RelatedTable.

Check this out.

 

Regards

Community Support Team
Community Support Team

Re: Summing of rows with values

Hi @ozgurozturkkk,

 

It is not available to show the value (02. Kostprijs omzet - 03. Salarissen) in "SubTotal" row. While you can get this value via measure, similar to:

Diff =
CALCULATE (
    SUM ( 'Table Y'[Bedrag] ),
    FILTER (
        ALLSELECTED ( 'Table X' ),
        'Table X'[GBR_ENT(groups)] = "02. Kostprijs omzet"
    )
)
    - CALCULATE (
        SUM ( 'Table Y'[Bedrag] ),
        FILTER (
            ALLSELECTED ( 'Table X' ),
            'Table X'[GBR_ENT(groups)] = "03. Salarissen"
        )
    )

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ozgurozturkkk Frequent Visitor
Frequent Visitor

Re: Summing of rows with values

Hi @v-yulgu-msft,

 

It seems like it's working. But what should i do when i have:

"02. Kostprijs Omzet"

"03. Salarissen"

(02. Kostprijs Omzet - 03. Salarissen) => your dax-code

 

And what if i have

"02. Kostprijs Omzet" (value=500)

"03. Salarissen" (value=1000)

(02. Kostprijs Omzet - 03. Salarissen) => your dax-code (value=1000-500 = 500)

"04. Fee" (value=850)

"05. Facility" (value= 150)

"06. ABC" (value=200)

MEASURE?

 

Now i want to to see the all the orginal groups with the value but I also want to use just 1 calcutatio so i can distract the groups 06. ABC - 05. Facility - 04. Fee and your dax-code?

 

Can you help me out?

Community Support Team
Community Support Team

Re: Summing of rows with values

Hi @ozgurozturkkk,

 

Do you want to calculate the difference every two groups? If so, please add a calculated column in Table X first.

Group Index = RIGHT(LEFT('Table X'[GBR_ENT (groups)],2),1)

1.PNG

 

Then, create measures:

Bedrag current group =
CALCULATE (
    SUM ( 'Table Y'[Bedrag] ),
    ALLEXCEPT ( 'Table X', 'Table X'[GBR_ENT (groups)] )
)

Bedrag next group =
CALCULATE (
    SUM ( 'Table Y'[Bedrag] ),
    FILTER (
        ALLSELECTED ( 'Table X' ),
        'Table X'[Group Index]
            = SELECTEDVALUE ( 'Table X'[Group Index] ) + 1
    )
)

diff between groups = [Bedrag current group]-[Bedrag next group]

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.