topic sum of the remaining in DAX Commands and Tips

sum of the remaining

Anonymous — Mon, 08 Aug 2022 11:55:35 GMT

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

Re: sum of the remaining

Greg_Deckler — Mon, 08 Aug 2022 12:32:18 GMT

Anonymous 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

Re: sum of the remaining

Anonymous — Mon, 08 Aug 2022 13:56:30 GMT

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.

Re: sum of the remaining

Greg_Deckler — Mon, 08 Aug 2022 14:06:10 GMT

Anonymous Where does the 400 come from?

Re: sum of the remaining

Anonymous — Mon, 08 Aug 2022 14:09:48 GMT

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

remaining

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

Thanks for your help.

Re: sum of the remaining

Anonymous — Mon, 08 Aug 2022 14:13:34 GMT

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

remaining

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

Thanks for your help.

Re: sum of the remaining

selinaz — Thu, 11 Aug 2022 06:50:38 GMT

Hi Anonymous,

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.

Re: sum of the remaining

Ethan96 — Thu, 11 Aug 2022 08:22:28 GMT

Hi, Anonymous

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