Calculate average of proportion by group

[Anonymous]

I'm trying to calculate the average of a proportion that was grouped by a category. Per the example below, average of  the proportion of "blank" category within a Fruit.

Data:

Fruit_ID	Category	Amount
1		5
1	A	6
2	A	7
2		2
2	B	3
3	A	0
3	B	5

The calculation would be:

Fruit_ID		A	B	Proportion
1	5	6		5/11=0.45
2	2	7	3	2/12=0.17
3		0	5	0/5=0.00

The average should be 0.31 averaging fruit 1,2, and 3. However, my formula only considers fruit 1 and 2 and calculates to 0.21, since they have values in the "blank" category and ignored Fruit 3. Technically my Fruit ID is also stored in another table and just has a join. Not sure how to reference the Fruit ID if it was stored in another table either.

What's wrong with my formula?

Avg Proportion =

AVERAGEX(

    KEEPFILTERS(VALUES('Table'[Fruit_ID])),

    CALCULATE(SUM('Table'[Amount]), ISBLANK(Table[Category]))/ CALCULATE(SUM('Table'[Amount]),) )

Thanks in advance!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

por1 =
CALCULATE (
    SUM ( 'Table1'[Amount] ),
    FILTER ( 'Table1', 'Table1'[Category] = BLANK () )
)
    / SUM ( 'Table1'[Amount] )

por2 =
VAR total =
    AVERAGEX ( VALUES ( Table1[Fruit_ID] ), [por1] )
RETURN
    IF ( ISINSCOPE ( Table1[Category] ), SUM ( Table1[Amount] ), total )

Or

por3 =
VAR total =
    AVERAGEX ( VALUES ( Table1[Fruit_ID] ), [por1] )
RETURN
    IF (
        ISINSCOPE ( Table1[Category] ),
        FORMAT ( SUM ( Table1[Amount] ), "0" ),
        FORMAT ( total, "0.00" )
    )

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

[parry2k]

@Anonymous can you share how does your raw data looks like? and how tables are related? 

[Anonymous]

hi @parry2k , I realized I don't need to reference or join another table, so that part is ok. But my raw data essentially looks like the data table I posted above. Or do you need more information?