cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Continued Contributor

## 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.

Continued Contributor

@Jihwan_Kim You are amazing. Thank you again.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors