Summing of rows with values

[Anonymous]

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?

[v-yulgu-msft]

Hi @Anonymous,

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

[Anonymous]

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?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

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

[ibarrau]

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

Check this out.

Regards