sum of the remaining

[Kim_Sky]

Hi, I'm new in Power BI and wondering to present the result in decomposition tree. 

I have a list of data 

Timestamp	type	value
6/6/22	a	50
7/6/22	b	70
8/6/22	c	80
4/6/22	d	90
3/6/22	e	40

And the sum of those are 400, but the sum from the table is 330. 

I use 

Remaining = sum('Total'[Value])-sum('Type'[Value])

How should I input this remaining value inside my decomposition tree?

The decomposition tree is total>type and remaining > a,b,c,d,e

Thanks for your help 

[selinaz · Accepted solution]

Hi @Kim_Sky,

Do you want this result?

please try this measure:

val = IF(HASONEVALUE('Table'[type]),SELECTEDVALUE('Table'[value]),'Table'[remaining])

and change the name of total:

you can get you want.

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

Best regards.

[Ethan96 · Accepted solution]

Hi, @Kim_Sky 

You need to complete Table 'categories list' with the following data.

Calculated column:

Category = IF('category list'[SubCategory]="remaining","remaining","Type")

Total= 
var a= RELATED('Type'[value])
return IF(ISBLANK(a),SUM(Total[Value])-SUM('Type'[value]),a)

sample pbix

Regards,

Ethan

[selinaz · Accepted solution]

Hi @Kim_Sky,

Do you want this result?

please try this measure:

val = IF(HASONEVALUE('Table'[type]),SELECTEDVALUE('Table'[value]),'Table'[remaining])

and change the name of total:

you can get you want.

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

Best regards.

[Greg_Deckler]

@Kim_Sky This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Kim_Sky]

Hi @Greg_Deckler , 

There something different. I want to use dax to get the remaining value 70 and put that value into the remaining column so that the time I perform decomposition tree there will be 330 and 70 from the total. 

How should I do that? 

Thanks for your help. 

[Greg_Deckler]

@Kim_Sky Where does the 400 come from?

[Kim_Sky]

I have two set of value; one is the total 400 and one is the list of value which equal to 330.  And I use the remaining=total-list of value.

So I willing to get a decomposition tree that 330 and 70. 

I have a category list table 

a

b

c

d

e

remaining

But I don't know how to let the remaining value input to the table value there. 

Thanks for your help. 

[Kim_Sky]

Hi @Greg_Deckler ,

I have two set of value; one is the total 400 and one is the list of value which equal to 330.  And I use the remaining=total-list of value.

So I willing to get a decomposition tree that 330 and 70. 

I have a category list table 

a

b

c

d

e

remaining

But I don't know how to let the remaining value input to the table value there. 

Thanks for your help.