cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sheap069
Helper III
Helper III

Median of Percentage Measure

Hi,

I have the following data, which can be found here

 

Data set

Application IDUser IDAttribute 1Attribute 2Attribute 3Attribute 4Data
110TRUEFALSEFALSETRUEData1
211FALSEFALSEFALSETRUEData1
312TRUEFALSEFALSETRUEData1
410FALSETRUEFALSETRUEData1
510FALSEFALSEFALSETRUEData1
612FALSETRUEFALSETRUEData1
711FALSEFALSETRUEFALSEData2
811TRUEFALSETRUEFALSEData2
913TRUETRUETRUEFALSEData2
1013FALSEFALSETRUEFALSEData2
1110FALSEFALSETRUEFALSEData2
1212TRUETRUETRUEFALSEData2

I've unpivoted the data to be set up like this

Application IDUser IDDataAttributeValue
110Data1Attribute 1TRUE
110Data1Attribute 2FALSE
110Data1Attribute 3FALSE
110Data1Attribute 4TRUE
211Data1Attribute 1FALSE
211Data1Attribute 2FALSE
211Data1Attribute 3FALSE
211Data1Attribute 4TRUE
312Data1Attribute 1TRUE
312Data1Attribute 2FALSE
312Data1Attribute 3FALSE
312Data1Attribute 4TRUE
410Data1Attribute 1FALSE
410Data1Attribute 2TRUE
410Data1Attribute 3FALSE
410Data1Attribute 4TRUE
510Data1Attribute 1FALSE
510Data1Attribute 2FALSE
510Data1Attribute 3FALSE
510Data1Attribute 4TRUE
612Data1Attribute 1FALSE
612Data1Attribute 2TRUE
612Data1Attribute 3FALSE
612Data1Attribute 4TRUE
711Data2Attribute 1FALSE
711Data2Attribute 2FALSE
711Data2Attribute 3TRUE
711Data2Attribute 4FALSE
811Data2Attribute 1TRUE
811Data2Attribute 2FALSE
811Data2Attribute 3TRUE
811Data2Attribute 4FALSE
913Data2Attribute 1TRUE
913Data2Attribute 2TRUE
913Data2Attribute 3TRUE
913Data2Attribute 4FALSE
1013Data2Attribute 1FALSE
1013Data2Attribute 2FALSE
1013Data2Attribute 3TRUE
1013Data2Attribute 4FALSE
1110Data2Attribute 1FALSE
1110Data2Attribute 2FALSE
1110Data2Attribute 3TRUE
1110Data2Attribute 4FALSE
1212Data2Attribute 1TRUE
1212Data2Attribute 2TRUE
1212Data2Attribute 3TRUE
1212Data2Attribute 4FALSE

I then made a table for the count of applications per user ID where Attribute 1 is true. 

 Data 1Data 2
 # of AppsRate# of Apps Rate
10133%00%
1100%150%
12150%1100%
13(blank)(blank)150%

To do this, I created a measure for the count and rate, and created a matrix for my table to be like this. 

 

Count of Application for Attribute1 =

CALCULATE(

    COUNTA('Append1'[APPLICATION_NO]),

    FILTER('Append1', 'Append1'[Attribute] = "Attribute1" && 'Append1'[Value]= "TRUE"))

 

rate = [Count of Application for Attribute1]/DISTINCTCOUNT(Append1[Application])

 

I want to get the mean and median of the percentages, so the median for Data 1 should be 0% and the median for the second table should be 75%.

The average of data 1 should be 27.7 and the average of data 2 should be 50%. 

Thank you very much

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @sheap069 ,

You can try these measures to calculate the average and median value.

Average = 
VAR tab =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Append1,
            "Count for Attrivute 1",
                VAR _c =
                    CALCULATE (
                        COUNT ( Append1[Application ID] ),
                        FILTER (
                            ALL ( 'Append1' ),
                            [Attribute] = "Attribute 1"
                                && [Value] = TRUE ()
                                && [User ID] = EARLIER ( Append1[User ID] )
                                && [Data] = EARLIER ( Append1[Data] )
                        )
                    )
                RETURN
                    IF ( [Value] = TRUE () && [Attribute] = "Attribute 1", _c, 0 )
        ),
        "rate",
            [Count for Attrivute 1]
                / CALCULATE (
                    DISTINCTCOUNT ( Append1[Application ID] ),
                    FILTER (
                        'Append1',
                        [User ID] = EARLIER ( Append1[User ID] )
                            && [Data] = EARLIER ( Append1[Data] )
                    )
                )
    )
VAR tb =
    SUMMARIZE (
        ADDCOLUMNS (
            tab,
            "AVG",
                SUMX ( FILTER ( tab, [Data] = EARLIER ( Append1[Data] ) ), [rate] )
                    / COUNTX (
                        FILTER (
                            SUMMARIZE ( tab, [User ID], [Data] ),
                            [Data] = EARLIER ( Append1[Data] )
                        ),
                        [User ID]
                    )
        ),
        [User ID],
        [Data],
        [AVG]
    )
RETURN
    MAXX ( tb, [AVG] )
Median = 
VAR tab =
    SUMMARIZE (
        ADDCOLUMNS (
            ADDCOLUMNS (
                Append1,
                "Count for Attrivute 1",
                    CALCULATE (
                        COUNT ( Append1[Application ID] ),
                        FILTER (
                            ALL ( 'Append1' ),
                            [Attribute] = "Attribute 1"
                                && [Value] = TRUE ()
                                && [User ID] = EARLIER ( Append1[User ID] )
                                && [Data] = EARLIER ( Append1[Data] )
                        )
                    )
            ),
            "rate",
                [Count for Attrivute 1]
                    / CALCULATE (
                        DISTINCTCOUNT ( Append1[Application ID] ),
                        FILTER (
                            'Append1',
                            [User ID] = EARLIER ( Append1[User ID] )
                                && [Data] = EARLIER ( Append1[Data] )
                        )
                    )
        ),
        [Data],
        [User ID],
        [rate]
    )
VAR tb =
    ADDCOLUMNS (
        tab,
        "Med", MEDIANX ( FILTER ( tab, [Data] = EARLIER ( Append1[Data] ) ), [rate] )
    )
RETURN
    MAXX ( tb, [Med] )

By the way, based on the matrix rate value and the median calculation logic, the median of Data1 seems to be 33% (0%,33%,50%) and the the median of Data2 seems to be 50% (0%,50%,50%,100%) instead of 0% and 75%.

AM.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @sheap069 ,

You can try these measures to calculate the average and median value.

Average = 
VAR tab =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Append1,
            "Count for Attrivute 1",
                VAR _c =
                    CALCULATE (
                        COUNT ( Append1[Application ID] ),
                        FILTER (
                            ALL ( 'Append1' ),
                            [Attribute] = "Attribute 1"
                                && [Value] = TRUE ()
                                && [User ID] = EARLIER ( Append1[User ID] )
                                && [Data] = EARLIER ( Append1[Data] )
                        )
                    )
                RETURN
                    IF ( [Value] = TRUE () && [Attribute] = "Attribute 1", _c, 0 )
        ),
        "rate",
            [Count for Attrivute 1]
                / CALCULATE (
                    DISTINCTCOUNT ( Append1[Application ID] ),
                    FILTER (
                        'Append1',
                        [User ID] = EARLIER ( Append1[User ID] )
                            && [Data] = EARLIER ( Append1[Data] )
                    )
                )
    )
VAR tb =
    SUMMARIZE (
        ADDCOLUMNS (
            tab,
            "AVG",
                SUMX ( FILTER ( tab, [Data] = EARLIER ( Append1[Data] ) ), [rate] )
                    / COUNTX (
                        FILTER (
                            SUMMARIZE ( tab, [User ID], [Data] ),
                            [Data] = EARLIER ( Append1[Data] )
                        ),
                        [User ID]
                    )
        ),
        [User ID],
        [Data],
        [AVG]
    )
RETURN
    MAXX ( tb, [AVG] )
Median = 
VAR tab =
    SUMMARIZE (
        ADDCOLUMNS (
            ADDCOLUMNS (
                Append1,
                "Count for Attrivute 1",
                    CALCULATE (
                        COUNT ( Append1[Application ID] ),
                        FILTER (
                            ALL ( 'Append1' ),
                            [Attribute] = "Attribute 1"
                                && [Value] = TRUE ()
                                && [User ID] = EARLIER ( Append1[User ID] )
                                && [Data] = EARLIER ( Append1[Data] )
                        )
                    )
            ),
            "rate",
                [Count for Attrivute 1]
                    / CALCULATE (
                        DISTINCTCOUNT ( Append1[Application ID] ),
                        FILTER (
                            'Append1',
                            [User ID] = EARLIER ( Append1[User ID] )
                                && [Data] = EARLIER ( Append1[Data] )
                        )
                    )
        ),
        [Data],
        [User ID],
        [rate]
    )
VAR tb =
    ADDCOLUMNS (
        tab,
        "Med", MEDIANX ( FILTER ( tab, [Data] = EARLIER ( Append1[Data] ) ), [rate] )
    )
RETURN
    MAXX ( tb, [Med] )

By the way, based on the matrix rate value and the median calculation logic, the median of Data1 seems to be 33% (0%,33%,50%) and the the median of Data2 seems to be 50% (0%,50%,50%,100%) instead of 0% and 75%.

AM.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

parry2k
Super User III
Super User III

@sheap069 use MEDIAX and AVERAGEX to calculate the respective value.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi,

I tried this by writing MEDIANX(Append1,[Rate]) and this isn't producing the correct result. It's giving me (blank). 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors