Forum Discussion

Neil_Cooper's avatar
Neil_Cooper
Icon for Helper I rankHelper I
5 years ago
Solved

Averagex by overall total (column total)

Hi Everyone

 

I am having a real challenge in Power BI.  Basically I have run an "Averagex" measure which is working fine technically.  But when I pull it into the Matrix/Table visual it is showing the average based on the rows, what I need is the average against the column total (overall total)

 

This is the current measure:  Average Customers per product = AVERAGEX(DISTINCT('Customer Products '[Product ID ID]),[Total Customers])

 

In the dummy data example below what I need is the average customers per product based on the total products for all countries.  Is there a way of doing this via a measure?

 

Any help will be greatly appreciated 🙂

 

Thanks

 

 

 

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Neil_Cooper 

    According to your description, I guess that you want to make the average measure to calculate the average based on the total instead of [country] (value is 45.1), right? I think you can try this measure:

    Average Customers per product =

var _sumofcust=SUMX(ALL('Table'),[Total Customers])

var _noofprod=Calculate(DISTINCTCOUNT('Table'[Product ID ID]), ALL('Table'))

return

DIVIDE(_sumofcust,_noofprod)

    Then go to the matrix and check the value again:

     

    And you can get what you want.

     

    If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.

    How to Get Your Question Answered Quickly 

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Neil_Cooper , Try like

    Average Customers per product = AVERAGEX(values('Customer Products '[Product ID]),[Total Customers])

     

    Or like

    try a measure like

    divide([Total Customers], distinct('Customer Products '[Product ID]))

    • Neil_Cooper's avatar
      Neil_Cooper
      Icon for Helper I rankHelper I
      5 years ago

      Thank you so much for the prompt reply and the measure examples.  Unfortunatly the first example is still giving me the result just showing the average against the rows and the second example is not displaying on the visual.  I am getting this message 😞 

       

      Any further guidance will be greatly appreciated!

       

       

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Neil_Cooper 

    According to your description, I guess that you want to make the average measure to calculate the average based on the total instead of [country] (value is 45.1), right? I think you can try this measure:

    Average Customers per product =

var _sumofcust=SUMX(ALL('Table'),[Total Customers])

var _noofprod=Calculate(DISTINCTCOUNT('Table'[Product ID ID]), ALL('Table'))

return

DIVIDE(_sumofcust,_noofprod)

    Then go to the matrix and check the value again:

     

    And you can get what you want.

     

    If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.

    How to Get Your Question Answered Quickly 

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

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