Forum Discussion
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 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)])
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])
5 Replies
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])
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.
Thanks for the reply. I edited the original question and added the DAX measure at the end.
