Forum Discussion
Solved
Issue with the Subtotal using DAX
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 th...
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])
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])
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.
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])