Average Subcategory sums

[Anonymous]

Hi,

I am looking to average the sum of a subcategory within a column in my dataset. See below for an example. I am looking to find the average deal size for renewals/new and display that measure in a card. In other words, the average renewal deal size measure should calculate ((10+40)+(35+24))/2 which would equal 54.5. I have thought about making a calculated table which summarize each deal and go that way but I would prefer this be done in one DAX measure. I've attached a link to a dummy dataset. Thank you in advance.  

https://www.dropbox.com/s/c43c65d11m07exo/Dummy%20Data.pbix?dl=0

Deal #	Type	Product	Price
Deal 1	Renewal	Product A	10
Deal 1	New	Product B	20
Deal 1	Renewal	Product C	40
Deal 1	New	Product D	60
Deal 2	New	Product E	15
Deal 2	Renewal	Product F	35
Deal 2	New	Product G	40
Deal 2	Renewal	Product H	24

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, you could try these two way:

1. Create two measure as below:

Renewal avg = 
var _table=SUMMARIZE(FILTER(Sheet1,Sheet1[Type]="Renewal"),Sheet1[Deal #],Sheet1[Type],"_value",CALCULATE(SUM(Sheet1[Price]))) return
AVERAGEX(_table,[_value])

New avg = 
var _table=SUMMARIZE(FILTER(Sheet1,Sheet1[Type]="New"),Sheet1[Deal #],Sheet1[Type],"_value",CALCULATE(SUM(Sheet1[Price]))) return
AVERAGEX(_table,[_value])

Then drag them into card visual.

2. create a measure and the drag type and it into a table visual

avg =
var _table=SUMMARIZE(Sheet1,Sheet1[Deal #],Sheet1[Type],"_value",CALCULATE(SUM(Sheet1[Price]))) return
AVERAGEX(_table,[_value])

Result:

here is sample pbix file, please try it.

Regards,

Lin

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Greg_Deckler]

Seems like:

Renewal Measure = AVERAGEX(SUMMARIZE(FILTER('Table',[Type] = "Renewal"),[Deal #],"__Total",SUM('Table'[Price])),[__Total])

New Measure = AVERAGEX(SUMMARIZE(FILTER('Table',[Type] = "New"),[Deal #],"__Total",SUM('Table'[Price])),[__Total])

[parry2k]

@Anonymous you can always achieve this with one measure and filter on any type

Avg Measure = AVERAGEX ( VALUES ( 'Table'[Deal #] ), CALCULATE( SUM ( 'Table'[Price] ) ) )

Avg Renewal = CALCULATE( AVERAGEX ( VALUES ( 'Table'[Deal #] ), CALCULATE( SUM ( 'Table'[Price] ) ) ), 'Table'[Type] = "Renewal" )

Avg New = CALCULATE( AVERAGEX ( VALUES ( 'Table'[Deal #] ), CALCULATE( SUM ( 'Table'[Price] ) ) ), 'Table'[Type] = "New" )

for single measure (Avg Measure), just use table visual and put type and avg measure in the visual, and it will show avg specific to the type. it is more scalable in case you have more types in the future. Would appreciate Kudos 🙂 if my solution helped. 

[camargos88]

Hi @Anonymous ,

Try creating this measure:

Measure = DIVIDE(SUMX(SUMMARIZE(FILTER('Table'; 'Table'[Type] = "Renewal"); 'Table'[Deal #]; 'Table'[Price]); 'Table'[Price]); CALCULATE(DISTINCTCOUNT('Table'[Deal #]); 'Table'[Type] = "Renewal"))

Ricardo