Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
dax_rach
Frequent Visitor

How to get Max of a subcategory

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!screenshot_sample.png

2 REPLIES 2
dax_rach
Frequent Visitor

campaignnamebrandnameinvest_
CampaignABrandG1051
CampaignKBrandF1000
CampaignGBrandA972
CampaignEBrandH939
CampaignBBrandB686
CampaignHBrandB681
CampaignEBrandF614
CampaignABrandE599
CampaignHBrandF596
CampaignBBrandF585
CampaignEBrandD560
CampaignIBrandD495
CampaignIBrandG484
CampaignBBrandH477
CampaignMBrandF464
CampaignIBrandI457

 

  • 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!

screenshot_er_diagram.pngscreenshot_tables.png

Mahesh0016
Super User
Super User

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.