Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX formula for MODE giving errors

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.