% of grand total of a measure shows incorrectly

[Anonymous]

I calculate a measure called total_dischrg, I would like to see the percentage number of the measure by each poverder, I put provider and measure in a table, but when I choose show as % of grand total, it shows incorrectly as shown below, some number is even > 100% which doesn't make sense. The total also is not correct.. Any solution for this issue? 

[v-angzheng-msft]

Hi, @Anonymous 

If the logic of the formula is correct, then you could try to replace the ALLEXCEPT function with Filter+All+Max:

FILTER ( ALL ( table ), 'table'[A] = MAX ( 'table'[A] ) )

I have occasionally encountered cases where the ALLEXCEPT function does not calculate correctly and FilterAllMax works.

like this:

If this does not work, then please consdier sharing more details about it and posting expected result or a simple sample file without any sesentive information for further discussion.  It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

final_dischrg is calculated by mutiply one column with other 2 measures together which shown below..

[PaulOlding]

Hi @Anonymous 

What's the code for final_dischrg measure?

[Anonymous]

And final_dischrg is calcualted by spe_ratio*zip_ratio* total_discharge(which is a column is the table). It may because the calculation of final_dischrg is also involved with provider_number?

[Anonymous]

Just noticed the screenshot is too small. I paste the formula below. Both of the two ratio are just a divend function combine some ALLEXCEPT

spe_ratio = CALCULATE(SUM(MUP_IHP_RY21_P02_V10_DY19_PrvSvc_0[Tot_Dschrgs]), ALLEXCEPT(MUP_IHP_RY21_P02_V10_DY19_PrvSvc_0,MUP_IHP_RY21_P02_V10_DY19_PrvSvc_0[Rndrng_Prvdr_CCN]), ALLEXCEPT(Main1,Main1[DRG Description]))/CALCULATE(SUM(MUP_IHP_RY21_P02_V10_DY19_PrvSvc_0[Tot_Dschrgs]), ALLEXCEPT(MUP_IHP_RY21_P02_V10_DY19_PrvSvc_0,MUP_IHP_RY21_P02_V10_DY19_PrvSvc_0[Rndrng_Prvdr_CCN]), ALLSELECTED(Main1[DRG Description]), ALL(Main1[DRG Description]))

zip_ratio = CALCULATE(CALCULATE(SUM('service area by hospital2020_final'[SumTOTAL_CASES]), ALLEXCEPT('service area by hospital2020_final', 'service area by hospital2020_final'[MEDICARE_PROV_NUM], 'service area by hospital2020_final'[ZIP_CODE], 'service area by hospital2020_final'[Home Office Name]))/CALCULATE(SUM('Service Area by hospital2020_final'[SumTOTAL_CASES]), ALLEXCEPT('service area by hospital2020_final', 'service area by hospital2020_final'[MEDICARE_PROV_NUM], 'service area by hospital2020_final'[Home Office Name])))