DAX formula for MODE giving errors

[Anonymous]

Hello All,
 
I am new to PowerBi and working on a DAX formula to calculate Mode of my data set. (see attached)
 
I got following formula from one of the sites, which works correctly except for few columns. I have mentioned the same in the sheet tab. Can someone help pinpoint why Excel Mode Formula and PowerBi Mode DAX is not matching for these columns whereas it matches for rest of the columns?

mode2 = VAR myTable = SUMMARIZE(Data,Data[Value],"Count",COUNT(Data[Value]))
VAR myTable1 = FILTER(myTable,[Count]=MAXX(myTable,[Count]))
VAR mode1 = MAXX(LASTNONBLANK(myTable1 ,[Value]),[Value])
RETURN mode1

Sample File

[Anonymous · Accepted solution]

Found the problem. It was not an error, but because that column had 2 modes, DAX was picking up the max among the two. Excel by default picks up minimum of the two. I just updated the formula as Maxx(firstnonblank(mytable1,[value]),[value]) and it now gives same result as excel file.

[v-cherch-msft]

Hi @Anonymous 

Could you share your sample data which could reproduce your scenario and your desired output?You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

[Anonymous]

Unfortunately I cant upload the PBIX because it has lot of sensitive data. I had uploaded the sample excel data sheet in the following link. Have a look and use the DAX i had posted before to check the error.

Sample File

Thanks,

AK