Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
It seems that the visual / matrix charts are filtering my data so that is excludes blanks so I cannot get the AVERAGE I desire. Here is an example:
Data Set:
Unique ID | Source | Value |
ABC1 | web | $2 |
ABC2 | phone | $2 |
ABC3 | web | $4 |
ABC4 | walk in |
|
ABC5 | web |
|
ABC6 | web |
|
ABC7 | phone |
|
ABC8 | walk in | $10 |
Average Per Source EXPECTED & DESIRED results that includes the "blanks" in the count for the denominator:
source | count | Sum of value | Desired Average Value |
web | 4 | $6 | $6/4=$1.50 |
phone | 2 | $2 | $2/2=$1.00 |
walk in | 2 | $10 | $10/2=$5.00 |
Total | 8 | $18 | $18/8=$2.25 |
What the MATRX VISUALIZATSION GIVES me when I select "Average" for the Value type:
source | count | Sum of value | Received Average Value |
web | 2 | $6 | $6/2=$3.00 |
phone | 1 | $2 | $2/1=$2.00 |
walk in | 1 | $10 | $10/1=$10.00 |
Total | 4 | $18 | $18/4=$4.5 |
Any ideas on how to get what I am looking for?
THANKS
You can create a measure using following DAX formula to get the desired result.
Average_Measure = CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Source] ) ) / CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Source] ) )
Best Regards,
Herbert
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |