Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I am trying to calculate the average total [Cost] per [Code] per [Name], while ignoring blank [Cost] values.
Here is an example of my goal:
I want to Sum [Cost] when [Code] equals "1" for "Brett", "Justin", and "Alex" individually, then take an average of the total. However, if one of the [Name] categories does not have a cost for the [Code], I do not want to include the [Name] category in the avaergae (thus: divide by 2, not 3).
Brett Code 1 Cost = 10+15+10 = 35
Justin Code 1 Cost = 10 + 10 = 20
Alex Code 1 Cost = blank (Thus, I do not want to take the average of 3 items, only 2)
Average total [Cost] for [Code] = "1" = (35+20)/2 = 27.5
However, because "Alex" has [Cost] for [Code] = 2, this average would be divided by 3.
Thank you for taking the time to read my question, I'll certainly give thumbs up and an accepted solution.
Name | Code | Cost |
Brett | 1 | 10 |
Brett | 1 | 15 |
Brett | 1 | 10 |
Brett | 1 | |
Brett | 2 | 10 |
Brett | 2 | 12 |
Brett | 2 | |
Justin | 1 | 10 |
Justin | 1 | 10 |
Justin | 2 | 5 |
Justin | 2 | 10 |
Justin | 2 | |
Justin | 2 | |
Justin | 2 | |
Alex | 2 | 15 |
Alex | 2 | 20 |
Alex | 2 | |
Alex | 2 |
Solved! Go to Solution.
@Anonymous,
Try these measures:
Sum Cost = SUM ( Table1[Cost] )
Average Cost =
VAR vTable =
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[Name], Table1[Code] ),
"@Cost", [Sum Cost]
)
VAR vResult =
AVERAGEX ( vTable, [@Cost] )
RETURN
vResult
Proud to be a Super User!
@Anonymous,
Try these measures:
Sum Cost = SUM ( Table1[Cost] )
Average Cost =
VAR vTable =
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[Name], Table1[Code] ),
"@Cost", [Sum Cost]
)
VAR vResult =
AVERAGEX ( vTable, [@Cost] )
RETURN
vResult
Proud to be a Super User!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |