cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## % of grand total of a measure shows incorrectly

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?

5 REPLIES 5
Community Support

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

Regular Visitor

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

Solution Sage

What's the code for final_dischrg measure?

Regular Visitor

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?

Regular Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.