Hi everyone,

I am new to this Power BI and DAX so I am looking for some help if possible. I am trying to build an in-memory pivot table and lay it out with categories on the rows and the actual amounts (for example) on the columns. The fact table has multiple accounts and I am trying to categorize the accounts. After doing this, most things are correct but one of the calculation is not correct. The gross profit should be the markup plus the total taxes. The problem is that one account I need to duplicate in two categories (billed time and WC). The gross profit calculation is omitting one of those amounts as that's the difference.

I am looking to also find how to change the column signs based on the order table before aggregation. So some signs need to be changed while others need to remain so I have the multiplier as 1 for those to remain.

Maybe there are better ways of doing what I am trying to do. I am open to all options.

Measure:

PivotAmount = 
SUMX (
    Mapping,
    CALCULATE (
        SUM ( 'Fact'[Amount] ),
        FILTER (
            ALL ( Mapping ),
            PATHCONTAINS ( Mapping[Path], EARLIER ( Mapping[ID], 1 ) )
        )
    ))

	Power BI	Looking for:
Headcount	91	91
Hours	10,247	10,247

Billed Time	(492,290)	492,290
Paid Time	423,349	(423,349)
Markup	(68,941)	68,941
Tax	27,616	(27,616)
WC	(290)	(290)
Benefit	(175)	175
Bus Tax	189	(189)
Total Tax	27,340	(27,920)
Gross Profit	(41,311)	41,021

Gross Profit (should be)	(41,601)
	290

Fact Table
Account	Date	Amount
1	1/1/2017	91
2	1/1/2017	10000
3	1/1/2017	247
4	1/1/2017	-490000
5	1/1/2017	-2000
6	1/1/2017	-290
8	1/1/2017	423349
10	1/1/2017	27000
11	1/1/2017	616
12	1/1/2017	-175
13	1/1/2017	189

Account Table
Account
1
2
3
4
5
6
8
10
11
12
13

Mapping Table
Account	Category	ChangeSign	ID	ParentID	Path
	Gross Profit		1	1	1
	Total Tax		2	1	1\|2
13	Bus Tax	-1	3	2	1\|2\|3
12	Benefit	-1	4	2	1\|2\|4
6	WC	1	5	2	1\|2\|5	Twice, once in billed time and also in WC
	Tax		6	2	1\|2\|6
11	_Tax	-1	7	6	1\|2\|6\|7
10	_Tax	-1	8	6	1\|2\|6\|8
	Markup		9	1	1\|9
8	Paid Time	-1	10	9	1\|9\|10
	Billed Time		11	9	1\|9\|11
6	_Billed Time	-1	12	11	1\|9\|11\|12	Twice, once in billed time and also in WC
5	_Billed Time	-1	13	11	1\|9\|11\|13
4	_Billed Time	-1	14	11	1\|9\|11\|14
	Hours		15		15
3	_Hours	1	16	15	15\|16
2	_Hours	1	17	15	15\|17
1	Headcount	1	18	18	18

Order Table
Category	Order	ChangeSign
Headcount	1	1
Hours	2	1
Billed Time	3	-1
Paid Time	4	-1
Markup	5	-1
Tax	6	-1
WC	7	1
Benefit	8	-1
Bus Tax	9	-1
Total Tax	10	-1
Gross Profit	11	-1

Forum Discussion

DAX Help

Dax

DAX

DAX Behavior

DAX Formula Help

DAX