cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
Helper I
Helper I

Thank you very much.

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

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


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.

 

 

 

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.


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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors