cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Averages - How can I do this?

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

2 REPLIES 2
Microsoft

## Re: Averages - How can I do this?

@H20treddr

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

Microsoft

## Re: Averages - How can I do this?

@H20treddr

Does above solution help?

Best Regards,
Herbert

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors