How to create a subtotal for Column on a table? My measure is not working.

[Anonymous]

Hello,

I am trying to get the subtotal for Net_Claim_Amt by PrimaryName and Year, but when I add my calculated measure, I don't think it works for PrimaryName. For the Year it does not work at all, somehow and some of the column numbers will disappear.

Can anybody please tell me how to fix this? 

My formulas -

Sub_Total_For_Year = CALCULATE([Net_Claim_Amt], ALLEXCEPT('XL-Hargrove_MpciPolicy_Data','XL-Hargrove_MpciPolicy_Data'[Year]))
Sub_Total_For_PrimaryName = CALCULATE([Net_Claim_Amt], ALLEXCEPT(Grower,Grower[ PrimaryName]))

Claim_amt = [Max_$_Guar] - [Harvest_Value]
Max_$_Guar = SUM('XL-Hargrove_MpciPremLine_Data'[Guar in lb or bu]) * [MAX_Price]
Harvest_Value = SUM(PriceKeys[ HarvestPrice]) * SUM('XL-Hargrove_ProductionReported_Data'[ TotalProductionAmount])

* MAX_Price also calculated Measure

Data with Subtotals, 

On this one, you can see it is not summing Net_Claim_Amount for the Primary name. I Just want to get  Total Net_Claim_Amount for the Primary name. 

Over here, When I add some of the column values are missing and it is not working. Same thing here, I just need the total Claim Amount by Year. 

Hopefully, all the above things make sense. Any help will really appreciate it. 

Thank you so much

[AllisonKennedy]

@Anonymous  You have a lot of bidirectional relationships. Without knowing your data, I can't be certain, but these are typically not needed and can cause problems. See this post and video here, it's the best explanation I've seen so far: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

I'll see if I can figure out your other problem, but you most likely need to fix your data model first.

[AllisonKennedy]

@Anonymous Can you please share screenshot of relationships for all these tables? And actually, on looking at this again you might have been right with ALLEXCEPT, sorry, it's the weekend and my brain is not firing on all cylinders! 😛

[Anonymous]

@AllisonKennedy  Thank you so much for the help. 

Please check on the link. I think you will get more ideas other than a screenshot. I have everything on my measure table. I also filter out for two Primary names  (Otherwise it will get messy). Please Ignore the Primary name, since this will be sample data (The primary name does not make any sense when I trim it down) 

https://drive.google.com/file/d/1heoTuzrvhC5yFHPGsxoGTe0ECzHS7gfQ/view?usp=sharing 

I really appreciate it if you could help me out with this. 

Again, Thank you 

[AllisonKennedy]

Try using ALL(Year) and ALL(PrimaryName) instead of ALLEXCEPT. ALLEXCEPT keeps the filter on the specified columns. It sound like you want to ignore the filter on PrimaryName and Year. Also, you should try adding Year from your DimDate table instead and see if that brings back the missing values. Use that in the ALL() function as well. 

[Anonymous]

Hi @AllisonKennedy 

Thank you so much, I tried after you telling me to do so, still no luck. It did not work for me.  I tired both Year And Primary name.  Please tell me if I done something wrong or any other idea. 
 

Sub_Total_For_PrimaryName = CALCULATE([Net_Claim_Amt], ALL(Grower[ PrimaryName]))
Sub_Total_For_Year = CALCULATE([Net_Claim_Amt], ALL('XL-Hargrove_MpciPolicy_Data'[Year]))

 Again, thank you so much for your time and help.