cancel
Showing results for
Did you mean:
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
Super Contributor

## 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

Super Contributor

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

@H20treddr

Does above solution help?

Best Regards,
Herbert

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 319 members 3,410 guests
Recent signins: