How to show average % and get the correct average % on Row subtotal line

[JustinDoh1]

I am sharing my PBI file here.

I am trying to display average calculation of percentage on the subtotal row and fix total shown as the correct 'average'.

80.79% and  32.95% are expected values.

For the 1st table (where there is missing of average), I am using these measures:

Budget % =

              calculate(

                            Divide(

                                        [Budget Count],

                                         calculate(

                                                       SELECTEDVALUE(Table2[Capacity])

                                                        )

                                        )

                              )

[Budget Count] = Count(Table1[ID]) + 0

 ----------------------------------------------------------------------------

Bottom are formulas for the measure 'Variance %' and corresponding measure 'Budget'.

Variance % = ([Budget] /  COUNTROWS(Table1))

 

Budget =

                calculate (

                               COUNTROWS(Table1),

                                 (Table1[PayerName]) in {"MA", "MHMO", "MMS", "Ins"}

                                )

How do I fix these two?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the attached file and the below picture.

Budget % = 
AVERAGEX (
    DISTINCT ( Table2[Provider_Name] ),
    CALCULATE ( DIVIDE ( COUNT ( Table1[ID] ), SUM ( Table2[Capacity] ) ) )
)

Variance % = 
AVERAGEX (
    DISTINCT ( JoinKeyNew[Facility Name] ),
    CALCULATE (
        DIVIDE (
            COUNTROWS (
                FILTER (
                    Table1,
                    Table1[PayerName]
                        IN { "Medicare A", "Medicare HMO", "Managed Medical Skilled", "Insurance" }
                )
            ),
            COUNTROWS ( Table1 )
        )
    )
)

[JustinDoh1]

@Jihwan_Kim You are amazing. Thank you again.