Custom roll up dax measure for parent child hierarchy

[BobbyKothari1]

Hello Community,

Need your help for below query.
Lets suppose there is one to many relationship between below two table and i want to make a DAX Measure that can flow amount as per parent child relationship between these two table but for few item, i want to create custom roll up measure like for net sales by adding [ 12,27,29] measure key and subtracting [31,32] from the fact_table amount .

I tried this below dax measure but i don't know why it's not working.

Measure Table

MeasureKey	Measure	MeasureParentKey	Measurepath	Level1	Depth	Level2	Level3
55	Adjustment	5	5|55	Revenue n Driver	2	Adjustment
35	Administrative Surcharge	12	5|12|35	Revenue n Driver	3	SER	Administrative Surcharge
27	AR Reserve	7	5|7|27	Revenue n Driver	3	Discount	AR Reserve
32	AR Writeoff	7	5|7|32	Revenue n Driver	3	Discount	AR Writeoff
34	Charged Fees	12	5|12|34	Revenue n Driver	3	SER	Charged Fees
8	Charged Hours	5	5|8	Revenue n Driver	2	Charged Hours
7	Discount	5	5|7	Revenue n Driver	2	Discount
29	ERP Reserve	7	5|7|29	Revenue n Driver	3	Discount	ERP Reserve
31	Fee Adjustments	7	5|7|31	Revenue n Driver	3	Discount	Fee Adjustments
9	NBP	5	5|9	Revenue n Driver	2	NBP
10	Net Sales	5	5|10	Revenue n Driver	2	NER
53	Net Adjusted Sales	5	5|53	Revenue n Driver	2	NER Adjusted
11	Net Revenue	5	5|11	Revenue n Driver	2	Net Revenue
363	Sales	12	5|12|363	Revenue n Driver	3	SER	NSR Amount
13	Partner Charged Hours	8	5|8|13	Revenue n Driver	3	Charged Hours	Partner Charged Hours
41	Realization Percent	5	5|41	Revenue n Driver	2	Realization Percent
5	Revenue n Driver		5	Revenue n Driver	1
54	RPH	5	5|54	Revenue n Driver	2	RPH
12	Gross sales	5	5|12	Revenue n Driver	2	SER
14	Staff Charged Hours	8	5|8|14	Revenue n Driver	3	Charged Hours	Staff Charged Hours

Fact Table

EngMeasureKey	Amount
9	17669
13	300457
14	1184776
27	71583
29	1349109
31	386686
32	54953
34	541787
35	142
55	11975
363	707926
9	35338
13	600914
14	2369552
27	143166
29	2698218
31	773372
32	109906
34	1083574
35	284
55	23950
363	1415852
9	56540.8
13	961462.4
14	3791283
27	229065.6
29	4317149
31	1237395
32	175849.6
34	1733718
35	454.4
55	38320
363	2265363

Thanks in advance

[amitchandak · Accepted solution]

@BobbyKothari1 , I have done that here. But you might not need the additional table I have

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...

[BobbyKothari1]

HI @amitchandak ,

Thanks for your content, it was really helpful.
I have also tried switch formula before, but i was n't using with filter and all formula.

appriciate your quick help.