cancel
Showing results for
Did you mean:
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)

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
Super Contributor

## Re: Summing of rows with values

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)`

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]```

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
Senior Member

## Re: Summing of rows with values

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

Check this out.

Regards

Super Contributor

## Re: Summing of rows with values

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.
Frequent Visitor

## Re: Summing of rows with values

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?

Super Contributor

## Re: Summing of rows with values

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)`

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]```

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.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 176 members 2,028 guests
Recent signins: