How do I show the same average calculation against each category?

[sveygal]

Hey guys, 

I need some help to figure out how I can achieve this desired behaviour in my measure. 

I have a measure calculating the National Average GP % across all of our branches: 

National Average GP% = 
    AVERAGEX(
    CALCULATETABLE(
        SELECTCOLUMNS(
            DISTINCT(BRANCHES[BRANCHNO]), 
            "BRANCHES", BRANCHES[BRANCHNO], 
            "Branch Margin", [Margin %]
        )
    ), 
    [Branch Margin]
)

The [Margin %] measure is a simple calculation:

Margin % = ([Total Sales]-[Total Cost])/[Total Sales]

This measure in a card visual gives us the average GP % value we expect. 

The problem is that I want to put this measure into a table with each [BRANCHNO] and have the measure show the same National Average GP % in each line. As you'd expect, it's producing the [Margin %] calculation or each branch. 

What I'm getting: 
Branch | National Average GP % 
0          |  41.5

1          |  53.6

2          |  39.2

3          |  44.9

What I'm looking for:

Branch | National Average GP % 
0          |  44.8

1          |  44.8

2          |  44.8

3          |  44.8

What do I need to do to achieve this? 

Thank you for help,

Swaren Veygal

[v-xicai]

Hi @sveygal ,

You may create measure like DAX below.

National Average GP% = AVERAGEX(ALL(BRANCHES), [Margin %])

Best Regards,

Amy

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

[sveygal]

Hi @v-xicai and @Anonymous ,

thank you very much for your help, however both your solutions come to the same outcome. I'm still not able to get the same national average value on every row in the table against each branch. 

I'll upload some sample data when I'm back at my desk, if you'd like to have another go at it. 

Thank you, 

Swaren Veygal

[Anonymous]

Hi @sveygal  , 

If I understand correctly, this should work:

National Average GP% = CALCULATE( [Margin %] , ALLEXCEPT( 'BRANCHES'[BRANCHNO] ))

Let me know how that goes.

Cheers,
Rob

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

[sveygal]

Unfortunately, this doesn't seem to the solve the problem. This measure ends up performing the [margin %] calculation for each branch and ignores the branch in context. 

Thank you for your help anyway. 

[Anonymous]

I think you are close to the correct result.

Maybe try this one: 

National Average GP% = 
    AVERAGEX(
    CALCULATETABLE(
        SELECTCOLUMNS(
            DISTINCT(BRANCHES[BRANCHNO]), 
            "BRANCHES", BRANCHES[BRANCHNO], 
            "Branch Margin", [Margin %]
        ),
      ALLEXCEPT( 'BRANCHES'[BRANCHNO])
    ), 
    [Branch Margin]
)

If that does not work please share sample data 🙂