cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
H20treddr Frequent Visitor
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
Highlighted
v-haibl-msft Super Contributor
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] ) )

Averages - How can I do this_1.jpg

 

Best Regards,
Herbert

v-haibl-msft Super Contributor
Super Contributor

Re: Averages - How can I do this?

@H20treddr

 

Does above solution help?

 

Best Regards,
Herbert

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 399 members 3,939 guests
Please welcome our newest community members: