Forum Discussion

rjsidek's avatar
rjsidek
Helper II
6 years ago

Getting Average based on Latest entry

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...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

put _min and _max in [ ]

rjsidek's avatar
rjsidek
Helper II
6 years ago

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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      rjsidek
      Helper II
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

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

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