How to get Max of a subcategory

[dax_rach]

Hi everyone. If someone could please help me. I have been trying to create a measure that tells me the Max value of a Category and the Max Value of its corresponding subcategory, but it has not worked so far.

I have 3 entities with the following columns (it has more, but these are the ones we are using)

Campaign:
[id_campaign] , [campaign_name]

Brand:

[id_brand], [brand_name]

Invest:
[fk_id_campaign], [fk_id_brand], [invest_]  (this in particular is a measure)

I have created this measure:
txt_campaign =
VAR maxInv = MAXX(Invest, [invest_])
VAR maxCampaign = MAXX(FILTER(Campaign, Campaign[campaign_name] & " - " & CALCULATE(FORMAT([invest_], "0"), Campaign) = FORMAT(maxInv, "0")), Campaign[campaign_name])
VAR maxBrand = MAXX(FILTER(VALUES(Brand), Brand[brand_name] & " - " & CALCULATE(FORMAT([invest_], "0"), FILTER(Campaign, Campaign[campaign_name] = maxCampaign), Brand) = FORMAT(maxInv, "0")), Brand[brand_name])

RETURN
maxInv & " - " & maxCampanha & " - " & maxBrand

But I am not getting the expected result, which should be 3202 - CampaignE - BrandH . CampaignE has the highest investment and within CampaignE, BrandH has the highest investment

I have attached the screenshot to better understand how the matrix is built.

Can someone please tell me what am I missing? What should I change? Thank you all!

[dax_rach]

campaignname	brandname	invest_
CampaignA	BrandG	1051
CampaignK	BrandF	1000
CampaignG	BrandA	972
CampaignE	BrandH	939
CampaignB	BrandB	686
CampaignH	BrandB	681
CampaignE	BrandF	614
CampaignA	BrandE	599
CampaignH	BrandF	596
CampaignB	BrandF	585
CampaignE	BrandD	560
CampaignI	BrandD	495
CampaignI	BrandG	484
CampaignB	BrandH	477
CampaignM	BrandF	464
CampaignI	BrandI	457

• Doesn't let me attach the pbix file. But here's a sample and some screenshots too of how it looks like.
• The expected result should be: 2113 - CampaignE - BrandH . CampaignE has the highest investment and within CampaignE, BrandH has the highest investment which is 939.
• invest_ = SUM(INVEST_[investment])

Please let me know if it helps. Thanks!

[Mahesh0016]

@dax_rach Please can you share your Pbix file or Dataset? Thank You.