Forum Discussion
Average based on distinct values in another column
This should work (tested locally):
Measure = AVERAGEX(SUMMARIZE(Table1, Table1[Employee], Table1[Age]), Table1[Age])
Hi bullius,
You can also create the measure using the formula.Measure = AVERAGEX(VALUES(Table[Employee]), CALCULATE(AVERAGE(Table[Age])))
Thanks,
Lydia Zhang
This should work (tested locally):
Measure = AVERAGEX(SUMMARIZE(Table1, Table1[Employee], Table1[Age]), Table1[Age])
Hey Jahida,
This looks like it can solve an issue that i'm experiencing.
However, how could it work if the column "Employee" is in another table?
And would it be any different if it's not neccessarily a number, but text?I'm not really sure what you mean... the original question wanted an average, what's an average of text values? Also if the columns are in different tables, how are they associated?
Right....it's a bit more complicated than i thought :)
Let's say i have 3 tables.
Table 1 - TransactionID - gives me the information about the transaction. Date, time, etc. ID is always unique. Details not included in the example - only IDTransaction ID
1 2 3 4 5 Table 2 - Transaction - Details of what items the transaction included, always referencing the ID, but extending over multiple rows, depending on the size of the transaction.
Transaction ID Item
1 Banana 1 Turnip 1 Chair 1 Banana 1 Banana 1 Apple 2 Turnip 2 Cabbage 2 Apple 3 Banana 3 Cucumber 3 Turnip 4 Banana 5 Chair Table 3 - Item ID - Extended information about the items that summarizes them in categories.
Item ID Item Category
11 Banana Fruit 12 Turnip Vegetable 13 Chair Furniture 14 Apple Fruit 15 Cabbage Vegetable 16 Cucumber Vegetable From the below screenshot, what i care about is the Distinct count for the Item Categories, meaning that i don't need to know how many of them are in total, but how many Transactions include which of the Categories (thus the DistinctCount, using Measure = DISTINCTCOUNT)
What i'm looking to find is the Average of each category Per Transaction. I want to be able to track the categories with the largest average per transaction (as well as the one with the lowest, and see trends, etc.)
So if i'm correct, that'd mean that the Average for Furniture should be 0.4 (2/5), Vegetables would be 0.6 (3/5) and Fruits would be 0.8 (4/5).
I have the feeling that this is very simple and it's a little bit that i'm missing, but i just can't get the right formulas to work for this.
The .pbix mock file can be found here, if needed.
Any help/tips will be appreciated!
Thanks!
Hello, older thread but taking a chance here... I have a very similar scenario, except that I need to include a additional parameter to the equation. To continue with the OP's example, I would also have another column for the employee gender, and need a formula that will calculate the average age of Male employees. How can this be done?
Thanks.
Try adding in a FILTER clause, e.g.
AVERAGEX ( SUMMARIZE ( FILTER ( Table1, Table1[Gender] = "Male" ), Table1[Employee], Table1[Age] ), Table1[Age] )
Awesome. Thanks a lot, it helped me.
