cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
soubhik
Helper I
Helper I

How to create an average of a category of a items from a table with a list of data of different cate

How to create an average of a category of a items from a table with a list of data of different categories?

 

Hi folks,

 

I have a table with following sample data:

 

Shop_City                        Shop           City             Nos  Nos Wgt Fruits/Schezwan

CostCo_New YorkCostCoNew York000%
CostCo_WashingtonCostCoWashington231192%
CostCo_ChicagoCostCoChicago11132%
CostCo_MarinCostCoMarin2312300%
CostCo_HoustonCostCoHouston121600%
CostCo_New YorkCostCoNew York000%
CostCo_WashingtonCostCoWashington121100%
CostCo_ChicagoCostCoChicago341100%
CostCo_MarinCostCoMarin000%
CostCo_HoustonCostCoHouston121600%
CostCo_New YorkCostCoNew York241100%
CostCo_WashingtonCostCoWashington121100%
CostCo_ChicagoCostCoChicago341100%
CostCo_MarinCostCoMarin11100%
CostCo_HoustonCostCoHouston21100%
CarreFour_New YorkCarreFourNew York561400%
CarreFour_WashingtonCarreFourWashington12175%
CarreFour_ChicagoCarreFourChicago000%
CarreFour_MarinCarreFourMarin201111%
CarreFour_HoustonCarreFourHouston18186%
CarreFour_New YorkCarreFourNew York161114%
CarreFour_WashingtonCarreFourWashington171106%
CarreFour_ChicagoCarreFourChicago211111%
CarreFour_MarinCarreFourMarin12167%
CarreFour_HoustonCarreFourHouston17181%
CarreFour_New YorkCarreFourNew York141100%
CarreFour_WashingtonCarreFourWashington161100%
CarreFour_ChicagoCarreFourChicago191100%
CarreFour_MarinCarreFourMarin181100%
CarreFour_HoustonCarreFourHouston211100%
Walmart_New YorkWalmartNew York201125%
Walmart_WashingtonWalmartWashington181138%
Walmart_ChicagoWalmartChicago000%
Walmart_MarinWalmartMarin221169%
Walmart_HoustonWalmartHouston251114%
Walmart_New YorkWalmartNew York12175%
Walmart_WashingtonWalmartWashington12192%
Walmart_ChicagoWalmartChicago000%
Walmart_MarinWalmartMarin161123%
Walmart_HoustonWalmartHouston20191%
Walmart_New YorkWalmartNew York161100%
Walmart_WashingtonWalmartWashington131100%
Walmart_ChicagoWalmartChicago221100%
Walmart_MarinWalmartMarin131100%
Walmart_HoustonWalmartHouston221100%
CostCo_New YorkCostCoNew York241100%
CostCo_WashingtonCostCoWashington371308%
CostCo_ChicagoCostCoChicago341100%
CostCo_MarinCostCoMarin2612600%
CostCo_HoustonCostCoHouston2811400%
CarreFour_New YorkCarreFourNew York501357%
CarreFour_WashingtonCarreFourWashington441275%
CarreFour_ChicagoCarreFourChicago291153%
CarreFour_MarinCarreFourMarin491272%
CarreFour_HoustonCarreFourHouston401190%
Walmart_New YorkWalmartNew York271169%
Walmart_WashingtonWalmartWashington311238%
Walmart_ChicagoWalmartChicago451205%
Walmart_MarinWalmartMarin411315%
Walmart_HoustonWalmartHouston271123%

 


The thing is that I want to create averages (ignoring zero values) of Schezwan/Juice, i.e.

Desired Results (Schezwan/Juice):

  CostCoCarrefourWalmartAverage
JuiceNew YorkxyzAverage(x,y,z)
JuiceWashingtonabcAverage(a,b,c)
JuiceChicagodefAverage(d,e,f)
JuiceMaringhiAverage(g,h,i)
JuiceHoustonjklAverage(j,k,l)

 

Attached is the:
Excel: https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing
PBIX: https://drive.google.com/file/d/1Y4rIOUl_tWdewzP8FPwPaqF9eQnAPTOO/view?usp=sharing

 

In the PBIX, I wrote a measure, which can be filtered to get Schezwan/Juice in the above matrix (i.e. z,y,z,a,b,c,d,e,f,g,h,i,j,k,l)

 

Measure = CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Schezwan"))/CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Juice"))

 

But I would like to create a simple average (ignoring zeroes) and without creating multiple measures and doing an average of that.  Any help in this regard would be extremely helpful.

 

Regards,

Soubhik.

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @soubhik 

 

I'd like to suggest you try the following measure. The pbix file is attached in the end.

Re = 
var tab = 
SUMMARIZE(
    Table1,
    Table1[City],
    Table1[Shop],
    "Result",
    DIVIDE(
        CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Schezwan")),
        CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Juice"))
    )
)
return
AVERAGEX(
    FILTER(
      tab,
      [Result]<>0
    ),
    [Result]
)

 

Result:

d1.png

 

Best Regards

Allan

 

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

 

View solution in original post

6 REPLIES 6
soubhik
Helper I
Helper I

Thank you very much.

v-alq-msft
Community Support
Community Support

Hi, @soubhik 

 

I'd like to suggest you try the following measure. The pbix file is attached in the end.

Re = 
var tab = 
SUMMARIZE(
    Table1,
    Table1[City],
    Table1[Shop],
    "Result",
    DIVIDE(
        CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Schezwan")),
        CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Juice"))
    )
)
return
AVERAGEX(
    FILTER(
      tab,
      [Result]<>0
    ),
    [Result]
)

 

Result:

d1.png

 

Best Regards

Allan

 

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

 

View solution in original post

AllisonKennedy
Super User III
Super User III

@soubhik  I have looked at the Excel file, but don't know which values you wish to use for x, y, z. What should the value of x be exactly? Which column are you wanting to average? Have you tried: 

 

Result = AVERAGEX(FILTER(Table, Table[Column] <> 0), Table[Column])



?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Oh, I am sorry it was not clear over there, but x is no of juice at costco at new york location. Attached are following screens:

soubhik_1-1607407214994.png  CostCo Schezwan by Juice in New York

 

soubhik_2-1607407315000.png  Averageif(<>0)

 

Updated in the excel as well: https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing

So, like here I dont want to create a new calc table or calc column, but if there can be a measure through which I can obtain the average measure. I was looking for that.

 

 

 

AllisonKennedy
Super User III
Super User III

@soubhik  I'm sorry, I don't fully understand what you want as end result. What do you mean by average of averages? It looks like you're comparing Schezwan to other fruits as a percent? To just get standard average, you can use the AVERAGE function, with Table[Shop] in Columns in the matrix. Please provide actual sample values, not just x, y, z and any details on what you mean by 'average' so we can assist further.



?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hi Allison,

 

I have attached the excel and PBIX in the original post with the data.

I represented what was percieved result in terms of 'x,y,z....' But the real data terms is available in the excel and PBIX attached.

 

Attached again:
Excel: https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing
PBIX: https://drive.google.com/file/d/1Y4rIOUl_tWdewzP8FPwPaqF9eQnAPTOO/view?usp=sharing

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.