Skip to main content
cancel
Showing results for 
Search instead 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

Reply
JustinDoh1
Post Prodigy
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'.

JustinDoh1_1-1664995861347.png

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
Jihwan_Kim
Super User
Super User

Hi,

Please check the attached file and the below picture.

Jihwan_Kim_0-1665026364229.png

 

 

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.


Click here and Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the attached file and the below picture.

Jihwan_Kim_0-1665026364229.png

 

 

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.


Click here and Go to My LinkedIn Page


@Jihwan_Kim You are amazing. Thank you again.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.