Forum Discussion

sveygal's avatar
sveygal
Frequent Visitor
6 years ago

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

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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      sveygal
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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 :)

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      sveygal
      Frequent Visitor
      6 years ago

      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