topic Re: % of grand total of a measure shows incorrectly in DAX Commands and Tips

% of grand total of a measure shows incorrectly

Anonymous — Fri, 12 Nov 2021 15:00:32 GMT

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?

Re: % of grand total of a measure shows incorrectly

PaulOlding — Fri, 12 Nov 2021 16:39:46 GMT

Hi Anonymous

What's the code for final_dischrg measure?

Re: % of grand total of a measure shows incorrectly

Anonymous — Fri, 12 Nov 2021 19:52:59 GMT

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

Re: % of grand total of a measure shows incorrectly

Anonymous — Fri, 12 Nov 2021 19:54:26 GMT

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])))

Re: % of grand total of a measure shows incorrectly

Anonymous — Fri, 12 Nov 2021 19:56:51 GMT

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?

Re: % of grand total of a measure shows incorrectly

v-angzheng-msft — Tue, 16 Nov 2021 03:13:14 GMT

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