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

Post Prodigy

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

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?

1 ACCEPTED SOLUTION
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Post Prodigy

@Jihwan_Kim You are amazing. Thank you again.