Median of Percentage Measure

[sheap069]

Hi,

I have the following data, which can be found here: 

Data set

Application ID	User ID	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Data
1	10	TRUE	FALSE	FALSE	TRUE	Data1
2	11	FALSE	FALSE	FALSE	TRUE	Data1
3	12	TRUE	FALSE	FALSE	TRUE	Data1
4	10	FALSE	TRUE	FALSE	TRUE	Data1
5	10	FALSE	FALSE	FALSE	TRUE	Data1
6	12	FALSE	TRUE	FALSE	TRUE	Data1
7	11	FALSE	FALSE	TRUE	FALSE	Data2
8	11	TRUE	FALSE	TRUE	FALSE	Data2
9	13	TRUE	TRUE	TRUE	FALSE	Data2
10	13	FALSE	FALSE	TRUE	FALSE	Data2
11	10	FALSE	FALSE	TRUE	FALSE	Data2
12	12	TRUE	TRUE	TRUE	FALSE	Data2

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

Application ID	User ID	Data	Attribute	Value
1	10	Data1	Attribute 1	TRUE
1	10	Data1	Attribute 2	FALSE
1	10	Data1	Attribute 3	FALSE
1	10	Data1	Attribute 4	TRUE
2	11	Data1	Attribute 1	FALSE
2	11	Data1	Attribute 2	FALSE
2	11	Data1	Attribute 3	FALSE
2	11	Data1	Attribute 4	TRUE
3	12	Data1	Attribute 1	TRUE
3	12	Data1	Attribute 2	FALSE
3	12	Data1	Attribute 3	FALSE
3	12	Data1	Attribute 4	TRUE
4	10	Data1	Attribute 1	FALSE
4	10	Data1	Attribute 2	TRUE
4	10	Data1	Attribute 3	FALSE
4	10	Data1	Attribute 4	TRUE
5	10	Data1	Attribute 1	FALSE
5	10	Data1	Attribute 2	FALSE
5	10	Data1	Attribute 3	FALSE
5	10	Data1	Attribute 4	TRUE
6	12	Data1	Attribute 1	FALSE
6	12	Data1	Attribute 2	TRUE
6	12	Data1	Attribute 3	FALSE
6	12	Data1	Attribute 4	TRUE
7	11	Data2	Attribute 1	FALSE
7	11	Data2	Attribute 2	FALSE
7	11	Data2	Attribute 3	TRUE
7	11	Data2	Attribute 4	FALSE
8	11	Data2	Attribute 1	TRUE
8	11	Data2	Attribute 2	FALSE
8	11	Data2	Attribute 3	TRUE
8	11	Data2	Attribute 4	FALSE
9	13	Data2	Attribute 1	TRUE
9	13	Data2	Attribute 2	TRUE
9	13	Data2	Attribute 3	TRUE
9	13	Data2	Attribute 4	FALSE
10	13	Data2	Attribute 1	FALSE
10	13	Data2	Attribute 2	FALSE
10	13	Data2	Attribute 3	TRUE
10	13	Data2	Attribute 4	FALSE
11	10	Data2	Attribute 1	FALSE
11	10	Data2	Attribute 2	FALSE
11	10	Data2	Attribute 3	TRUE
11	10	Data2	Attribute 4	FALSE
12	12	Data2	Attribute 1	TRUE
12	12	Data2	Attribute 2	TRUE
12	12	Data2	Attribute 3	TRUE
12	12	Data2	Attribute 4	FALSE

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

	Data 1	Data 2
	# of Apps	Rate	# of Apps	Rate
10	1	33%	0	0%
11	0	0%	1	50%
12	1	50%	1	100%
13	(blank)	(blank)	1	50%

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

[v-yingjl · Accepted solution]

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

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.

[parry2k]

@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.⚡

[sheap069]

Hi,

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