Average of other measure - is this possible?

[ponczula]

Dear Memebrs, 

sicne couple of days I'm struggling with PBI on how to calucalte the average of another measure? 

Case as follows:

I do have a table with following meaures:

1. Sales per rep
2. Rep Count (distinct count)
3. Business Count (distinct count - how many business given rep supports)
4. Average of business supported by a rep

Table layout

Measure are calculated as follows:

OURep

The only thing I want to achieve is somehow re-calcualte avrage on Manager level to show average of all averages OR sum of all distinct counts on OU level divided by sum of all reps - desired view below:

Desired view

I'm totally vulenrable now - thought it's easy-peasy task but stucked with it for days... Help, please anyone!

[ponczula · Accepted solution]

Hello, 

thank for you help - already managed to find out the solution for my problem - works like a charm now!

# OU* = 
COUNTX (
    FILTER (
        SUMMARIZE (
            'DB',
            'DB'[Scenario],
            'DB'[Sales Rep Number],
            'DB'[OU Hierarchy],
            "Count OU", COUNT ( 'DB'[OU Hierarchy] ),
            "Sales Rep", SUM ( 'DB'[USD] )
        ),
        'DB'[Scenario] = "CY_ACT"
            && [Sales Rep] > 0
    ),
    [Count OU]
)

[v-xinruzhu-msft]

Hi @ponczula 

You can refer to the following solution.

Sample data 

Create a measure

Measure =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Manager],
        [Column1],
        "Average", AVERAGE ( 'Table'[Column3] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Column1] ),
        CALCULATE ( AVERAGE ( 'Table'[Column3] ) ),
        AVERAGEX ( FILTER ( a, [Manager] IN VALUES ( 'Table'[Manager] ) ), [Average] )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You cannot measure a measure.  Instead, implement your business logic in a single measure.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...