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
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 ID
Transaction 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!
First of all, that was a really good clarification post, thank you for making it easy to understand your situation.
I think the measure that you had (DISTINCTCOUNT) was close, what you wanted was just some way to divide by total transactions. There's probably a few ways to do this, I'm a bit rusty so the first one that came to my mind was using CALCULATE. Here's the measure I used:
Measure 2 = DISTINCTCOUNT('Transaction'[Transaction ID])/CALCULATE(DISTINCTCOUNT('Transaction'[Transaction ID]), ALL('ItemID'[Category]))
Note that the first bit is the same as your measure, and then you divide it by the count of transactions across all categories. It gives the results you said were the expected (you might need to delete and remake your measure so it reformats). I hope that helps!
Amazing! This is exactly what i was looking for!
Thanks for your help, especially considering this is already a solved topic and you won't get the credit ;)
Have a lovely weekend ahead and holidays if you're celebrating!Haha no problem, glad to help. Like I said, you made it easy. Happy Holidays to you too!