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 |
+1
