Get all values to be absolute numbers, but get sum correct

[Anonymous]

Hi,

I'm trying to figure out how i might be able to have all accounts showing absolute numbers, but still getting the hierarchy sum to be correct. 

below is just a snapshot of an example of how it looks like now, but would like all numbers to be absolute.
Its just a sum of General ledger amount

I see this could be an issue in case EBITDA or other summaries becomes negative, then I would like them to show negative still, but on account level all should show absolute numbers. Is this possible?

Thanks!

[mahoneypat · Accepted solution]

You should be able to use ISINSCOPE() to get your result.  For example,

NewMeasure = IF(ISINSCOPE(HierarchyTable[Account_no]), ABS(SUM(Fact[Amount])), SUM(Fact[Amount]))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-kelly-msft]

Hi @Anonymous ,

I have a workaround for you:

First create a table as below:

Then union Total with other rows in table [Revenue] using below dax expression:

Union table = UNION(ALL('Table'[Revenue ]),'Table (2)')

Create a relationship between the union table and the original table;

Finally create a measure as below:

Measure = 
IF(MAX('Union table'[Revenue ]) in FILTERS('Table'[Revenue ]),IF(MAX('Table'[Value])<0,ABS(MAX('Table'[Value])),MAX('Table'[Value])),SUMX(ALL('Table'),'Table'[Value]
))

And you will see; (Set subtotals:off)

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Wow, this is great, thanks!
Unfortunately, I don't think its exactly what I would like. I have a table with accounting setup, different classes based on account level, currently using account number and class 3 in a hierarchy where class 3 sums up every account in that class.
I might be misunderstanding, but if I'm using this setup not sure if your solution works..

Thanks!

[Anonymous]

Hi @Anonymous ,

Can you provide some sample data for me to test?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi,

I have uploaded a sample pbix file here

If that link is not working, the two sample tables i have created are below:

Account_no	class 3	class 3 no
1000	Revenue	1
1001	Revenue	1
1003	Revenue	1
1005	Revenue	1
1006	Revenue	1
1007	Revenue	1
1008	Revenue	1
1101	COGS	2
1102	COGS	2
1105	COGS	2
1120	COGS	2
1150	COGS	2
1300	Salary	3
1301	Salary	3
1302	Salary	3
1309	Salary	3

account no	Amount
1000	1000
1000	123
1000	1244
1000	1241
1000	12
1000	2423
1000	-12
1001	242
1001	-123
1001	-122
1001	-532
1001	21
1001	98
1003	123
1003	432
1003	4322
1005	1234
1006	123
1006	1
1006	2
1007	3
1007	4
1008	5
1008	24
1008	234
1101	-123
1101	-1243
1101	-21
1101	-234
1101	-21
1101	-23
1101	-234
1102	-21
1102	22
1120	-214
1120	-23
1120	-23
1150	-12
1150	-24
1150	-21
1150	-144
1300	-21
1300	-21
1300	-42
1301	-234
1301	-2
1302	21
1302	-32
1302	-33
1309	-42
1309	-2
1309	-4

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can use "Isinscope"function to realize it,measure is as below:

Measure = IF(ISINSCOPE('Table (2)'[Amount]),ABS(MAX('Table (2)'[Amount])),SUMX(ALL('Table (2)'),'Table (2)'[Amount]))

Finally you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat · Accepted solution]

You should be able to use ISINSCOPE() to get your result.  For example,

NewMeasure = IF(ISINSCOPE(HierarchyTable[Account_no]), ABS(SUM(Fact[Amount])), SUM(Fact[Amount]))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat