Getting Average based on Latest entry

Question

Hi everyone,I have a dataset that looks like this&nbsp;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.&nbsp;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.&nbsp;amitchandak&nbsp;parry2k&nbsp;

amitchandak · Answer

Link : https://www.dropbox.com/s/nhqat3sfzupt3yc/dealsize.pbix?dl=0formula : you might have to change as per needTotal = Calculate(
    sumx(   
            SUMMARIZE(
                data,
                data[company],
				data[geography],
				"_Min",
				minx( filter(data,data[Deal Size(min)]&gt;0),data[date]),
                "_Max",
				minx(filter(data,data[Deal Size(max)]&gt;0),data[date]),
				"_comp",
				max(data[company]),
				"_geo",
				max(data[geography])
            ),
            divide(minx(filter(data,data[company]=[_comp] &amp;&amp; data[geography]=[_geo] &amp;&amp; data[date]= [_Min]),data[Deal Size(min)])+maxx(filter(data,data[company]=[_comp] &amp;&amp; data[geography]=[_geo] &amp;&amp; data[date]= [_Max]),data[Deal Size(max)] ),(if(isblank([_Max]),0,1)+if(isblank([_Min]),0,1)))

)
)&nbsp;&nbsp;

amitchandak · Answer

Try, Some thing like&nbsp;Total = Calculate(
    sumx(   
            SUMMARIZE(
                data,
                data[company],
				data[geography],
				"_Min",
				minx( filter(data,data[Deal Size(min)]&gt;0,data[date])),
                "_Max"
				minx( filter(data,data[Deal Size(max)]&gt;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-with-Winners/ba-p/890814https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739 https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

rjsidek · Answer

Hi&nbsp;amitchandak&nbsp;&nbsp;Have tried implementing the code you suggested, but I got an error. Below is a screenshot:&nbsp;&nbsp;Any ideas on how to rectify this?&nbsp;&nbsp;

amitchandak · Answer

put _min and _max in [ ]

rjsidek · Answer

Hi amitchandak&nbsp;&nbsp;Is this for the bottom line after the "Divide" function? I put them in [ ] like so&nbsp;divide([_Min]+[_Max],(if(isblank[_Max]),0,1)+if(isblank([_Min]),0,1)))&nbsp;but am still receiving the same errors&nbsp;

Forum Discussion

Getting Average based on Latest entry

7 Replies

Related content

Dax: How to get Average of Averages

Get latest date values

Get an Latest date Records

Average and Moving Average

Weekly Average