Issue with the Subtotal using DAX

[arashaga]

I have the folowing table ( partial). 

I use the following DAX measure to get the sum of [# Change 2014-2024] per Occupation. Why I am doing this is becuase I I do not do that for the occupation "Secondary School Teachers, Ex Special/Carrer/Technical Ed" I would get double since  [# Change 2014-2024] is repeted twice based on the [Major (CIP Title)] column for "Multi-/Interdisciplinary Studies, Other. (Primarily Education Majors)" and "Biology/Biological Sciences, General.". Everything comes up good in the matrix table except for the total that should be 11,480 bot 7,110.00 shown below. I guess it had to do with my DAX expression but I don't know how to fix it. Any Ideas?

 Below is the DAX Measure.

Total Demand = sum(UTSATEST[# Change 2014-2024])/DISTINCTCOUNT(UTSATEST[Major (CIP Title)])

[Greg_Deckler · Accepted solution]

OK, so probably something like:

Total Demand Intermediate = sum(UTSATEST[# Change 2014-2024])/DISTINCTCOUNT(UTSATEST[Major (CIP Title)])

Total Demand = 
IF(
  HASONEVALUE(UTSATEST[Occupation]),
    [Total Demand Intermediate],
    SUMX(SUMMARIZE(UTSATEST,[Occupation],"__Total",[Total Demand Intermediate]),[__Total])

[arashaga]

Wow. My head is spinning a bit here. Thank you. Is it possible to have all of this as one measure? ( I am sure you have tried to figure that out before posting) but this works.

[Greg_Deckler]

You could, but in my opinion, best practice would be to keep it separate in order to avoid repeating code.

Total Demand Intermediate = sum(UTSATEST[# Change 2014-2024])/DISTINCTCOUNT(UTSATEST[Major (CIP Title)])

Total Demand = 
IF(
  HASONEVALUE(UTSATEST[Occupation]),
    sum(UTSATEST[# Change 2014-2024])/DISTINCTCOUNT(UTSATEST[Major (CIP Title)])
,
    SUMX(SUMMARIZE(UTSATEST,[Occupation],"__Total",sum(UTSATEST[# Change 2014-2024])/DISTINCTCOUNT(UTSATEST[Major (CIP Title)])
),[__Total])

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Would need to see your formula for your measure.

[arashaga]

Thanks for the reply. I edited the original question and added the DAX measure at the end.