Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 York | CostCo | New York | 0 | 0 | 0% |
CostCo_Washington | CostCo | Washington | 23 | 1 | 192% |
CostCo_Chicago | CostCo | Chicago | 11 | 1 | 32% |
CostCo_Marin | CostCo | Marin | 23 | 1 | 2300% |
CostCo_Houston | CostCo | Houston | 12 | 1 | 600% |
CostCo_New York | CostCo | New York | 0 | 0 | 0% |
CostCo_Washington | CostCo | Washington | 12 | 1 | 100% |
CostCo_Chicago | CostCo | Chicago | 34 | 1 | 100% |
CostCo_Marin | CostCo | Marin | 0 | 0 | 0% |
CostCo_Houston | CostCo | Houston | 12 | 1 | 600% |
CostCo_New York | CostCo | New York | 24 | 1 | 100% |
CostCo_Washington | CostCo | Washington | 12 | 1 | 100% |
CostCo_Chicago | CostCo | Chicago | 34 | 1 | 100% |
CostCo_Marin | CostCo | Marin | 1 | 1 | 100% |
CostCo_Houston | CostCo | Houston | 2 | 1 | 100% |
CarreFour_New York | CarreFour | New York | 56 | 1 | 400% |
CarreFour_Washington | CarreFour | Washington | 12 | 1 | 75% |
CarreFour_Chicago | CarreFour | Chicago | 0 | 0 | 0% |
CarreFour_Marin | CarreFour | Marin | 20 | 1 | 111% |
CarreFour_Houston | CarreFour | Houston | 18 | 1 | 86% |
CarreFour_New York | CarreFour | New York | 16 | 1 | 114% |
CarreFour_Washington | CarreFour | Washington | 17 | 1 | 106% |
CarreFour_Chicago | CarreFour | Chicago | 21 | 1 | 111% |
CarreFour_Marin | CarreFour | Marin | 12 | 1 | 67% |
CarreFour_Houston | CarreFour | Houston | 17 | 1 | 81% |
CarreFour_New York | CarreFour | New York | 14 | 1 | 100% |
CarreFour_Washington | CarreFour | Washington | 16 | 1 | 100% |
CarreFour_Chicago | CarreFour | Chicago | 19 | 1 | 100% |
CarreFour_Marin | CarreFour | Marin | 18 | 1 | 100% |
CarreFour_Houston | CarreFour | Houston | 21 | 1 | 100% |
Walmart_New York | Walmart | New York | 20 | 1 | 125% |
Walmart_Washington | Walmart | Washington | 18 | 1 | 138% |
Walmart_Chicago | Walmart | Chicago | 0 | 0 | 0% |
Walmart_Marin | Walmart | Marin | 22 | 1 | 169% |
Walmart_Houston | Walmart | Houston | 25 | 1 | 114% |
Walmart_New York | Walmart | New York | 12 | 1 | 75% |
Walmart_Washington | Walmart | Washington | 12 | 1 | 92% |
Walmart_Chicago | Walmart | Chicago | 0 | 0 | 0% |
Walmart_Marin | Walmart | Marin | 16 | 1 | 123% |
Walmart_Houston | Walmart | Houston | 20 | 1 | 91% |
Walmart_New York | Walmart | New York | 16 | 1 | 100% |
Walmart_Washington | Walmart | Washington | 13 | 1 | 100% |
Walmart_Chicago | Walmart | Chicago | 22 | 1 | 100% |
Walmart_Marin | Walmart | Marin | 13 | 1 | 100% |
Walmart_Houston | Walmart | Houston | 22 | 1 | 100% |
CostCo_New York | CostCo | New York | 24 | 1 | 100% |
CostCo_Washington | CostCo | Washington | 37 | 1 | 308% |
CostCo_Chicago | CostCo | Chicago | 34 | 1 | 100% |
CostCo_Marin | CostCo | Marin | 26 | 1 | 2600% |
CostCo_Houston | CostCo | Houston | 28 | 1 | 1400% |
CarreFour_New York | CarreFour | New York | 50 | 1 | 357% |
CarreFour_Washington | CarreFour | Washington | 44 | 1 | 275% |
CarreFour_Chicago | CarreFour | Chicago | 29 | 1 | 153% |
CarreFour_Marin | CarreFour | Marin | 49 | 1 | 272% |
CarreFour_Houston | CarreFour | Houston | 40 | 1 | 190% |
Walmart_New York | Walmart | New York | 27 | 1 | 169% |
Walmart_Washington | Walmart | Washington | 31 | 1 | 238% |
Walmart_Chicago | Walmart | Chicago | 45 | 1 | 205% |
Walmart_Marin | Walmart | Marin | 41 | 1 | 315% |
Walmart_Houston | Walmart | Houston | 27 | 1 | 123% |
The thing is that I want to create averages (ignoring zero values) of Schezwan/Juice, i.e.
Desired Results (Schezwan/Juice):
CostCo | Carrefour | Walmart | Average | ||
Juice | New York | x | y | z | Average(x,y,z) |
Juice | Washington | a | b | c | Average(a,b,c) |
Juice | Chicago | d | e | f | Average(d,e,f) |
Juice | Marin | g | h | i | Average(g,h,i) |
Juice | Houston | j | k | l | Average(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.
Solved! Go to Solution.
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much.
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@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])
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:
CostCo Schezwan by Juice in New York
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.
@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.
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |