Getting Average based on Latest entry

[rjsidek]

Hi everyone,

I have a dataset that looks like this

What I am trying to do is create a measure in PowerBI to get the Average Deal Size for a company. Basically taking Deal Size min+ deal size max and dividing by 2. However, note that there are multiple entries in the deal size columns, meaning that these deal sizes change over time. I want to create an average measure that takes the latest entries by date. It gets more complicated because there might e circumstances where only one of the deal size comuns are filled ie. there is a value in deal size max but none in deal size min.

Is there a measure I can use to solve this issue? Have been tring to crack this for days now. Please help if you can.

@amitchandak @parry2k 

[amitchandak]

Try, Some thing like

Total = Calculate(
    sumx(   
            SUMMARIZE(
                data,
                data[company],
				data[geography],
				"_Min",
				minx( filter(data,data[Deal Size(min)]>0,data[date])),
                "_Max"
				minx( filter(data,data[Deal Size(max)]>0,data[date])),
				"_comp"
				max(data[company]),
				"_geo"
				max(data[geography])
            ),
            calculate(
                divide(_Min+_Max,(if(isblank(_Max),0,1)+if(isblank(_Min),0,1)))
                
            
    )
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[rjsidek]

Hi @amitchandak 

Have tried implementing the code you suggested, but I got an error. Below is a screenshot:

 Any ideas on how to rectify this?

[amitchandak]

put _min and _max in [ ]

[rjsidek]

Hi @amitchandak 

Is this for the bottom line after the "Divide" function? I put them in [ ] like so

divide([_Min]+[_Max],(if(isblank[_Max]),0,1)+if(isblank([_Min]),0,1)))

 but am still receiving the same errors 

[amitchandak]

Link : https://www.dropbox.com/s/nhqat3sfzupt3yc/dealsize.pbix?dl=0

formula : you might have to change as per need

Total = Calculate(
    sumx(   
            SUMMARIZE(
                data,
                data[company],
				data[geography],
				"_Min",
				minx( filter(data,data[Deal Size(min)]>0),data[date]),
                "_Max",
				minx(filter(data,data[Deal Size(max)]>0),data[date]),
				"_comp",
				max(data[company]),
				"_geo",
				max(data[geography])
            ),
            divide(minx(filter(data,data[company]=[_comp] && data[geography]=[_geo] && data[date]= [_Min]),data[Deal Size(min)])+maxx(filter(data,data[company]=[_comp] && data[geography]=[_geo] && data[date]= [_Max]),data[Deal Size(max)] ),(if(isblank([_Max]),0,1)+if(isblank([_Min]),0,1)))
                
            
    )
)

[rjsidek]

@amitchandak ,

Thanks for the pbi file. I looked at it and it seems correct. However, instead of calculating the average for each column, is there a way for it to only take the average based on latest entries of deal size max and min? Right now it seems like it is taking the average for every row instead of only the latest one

[amitchandak]

Check it like second table. Should now be there in link.

Now instead of sumx , you might want averagex for GT.