Sumx total in each row & total

[sb991986]

Hi,

In the following image, How can i get the total for a new column as 1300 (Sum of 800+500) but individual row as 800, 500 group by header id (see image below)?

Thanks in advance!

[v-xulin-mstf]

Hi @sb991986

Try meausre as:

Measure = 
var _sum=
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Header ID]=MAX('Table'[Header ID])
    ),
    'Table'[Revenue]
)
return 
IF(
    HASONEVALUE('Table'[Revenue]),
    _sum,
    SUM('Table'[Revenue])
    )

Here is the output:

The pbix is attached.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[selimovd · Accepted solution]

Hey @sb991986 ,

for a measure and also for a calculated column the following formula should work:

New Column =
CALCULATE(
    SUM( myTable[Revenue] ),
    ALLEXCEPT( myTable, myTable[Header ID] )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[sb991986]

Hi,

In the above example, I would need total as 1300 but individual row as 800, 500 by header id & detail id. Thank you

[amitchandak]

@sb991986 ,

calculate(sum(Table[revenue]), allselected(Table))

or

calculate(sum(Table[revenue]), all(Table))

[sb991986]

Hi,

In the above example, I would need total as 1300 but individual row as 800, 500 by header id & detail id

[amitchandak]

@sb991986 , Try like

sumx(values(Table[Header ID]), calculate(sum(Table[revenue]), filter(allselected(Table), Table[Header ID] = max(Table[Header ID]))))

[sb991986]

I tried your logic but it didn't work. Thank you fro your help