Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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
Super User

@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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.