Reply
Frequent Visitor
Posts: 7
Registered: ‎08-18-2017

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  

Super Contributor
Posts: 4,722
Registered: ‎07-08-2016

Re: Averages - How can I do this?

[ Edited ]

@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

Attachment
Super Contributor
Posts: 4,722
Registered: ‎07-08-2016

Re: Averages - How can I do this?

@H20treddr

 

Does above solution help?

 

Best Regards,
Herbert