Calculating Mode & Number of items for Mode

[awalsh]

Hi, 

1. I have a Dax formula that is calculating the Mode, however, it fails to account for when there is no mode (all values appearing once) it just lists the smallest value as the mode. Is there a formula that would identify situations where there is no mode? 

2. Also, is it possible to have a formula count the number of items that make up the mode? 

Thank you in advance! 

Here is the formula I'm currently using: 

Mode  =

MINX(

TOPN(1,

ADDCOLUMNS(

VALUES('NJ2000_LOS_Extract-All'[LOS in Months]),

"frequency", CALCULATE(COUNT('NJ2000_LOS_Extract-All'[LOS in Months]))),

[frequency],

0),

'NJ2000_LOS_Extract-All'[LOS in Months])

[v-yalanwu-msft]

Hi , @awalsh ;

According to your description, you could create column and measure as follows:

1) create a column to calculate the count .

count = CALCULATE(COUNT([Mode]),FILTER(ALL('Table'),[month]=EARLIER([month])&&[Mode]=EARLIER([Mode])))

2)create a column 

Column = 
var _a=MAXX(FILTER('Table',[month]=EARLIER([month])),[count])
var _b=MINX(FILTER('Table',[month]=EARLIER([month])),[count])
return IF(_a=_b,"N/A",IF(_a=[count],FORMAT([Mode],"General Number")))

3)create a measure

Measure = IF(ISINSCOPE('Table'[ID]),SUM([Mode]),MAX([Column]))

4)create another measure

Measure2 = IF(ISINSCOPE('Table'[ID]),SUM([Mode]),MAX([count]))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[awalsh]

Thank you so much for your help. I really appreciate it. This is exactly what I'm trying to do - however, 

When I try to create the first two DAX columns that you provided, I'm getting an error because my Mode values are being calculated using a measure. It seems that the formula isn't allowing me to reference a measure, it's asking me for a column. 😞 Any suggestions? 

[v-yalanwu-msft · Accepted solution]

Hi, @awalsh ;

If your Mode value is a measure ,you could modify the two dax as follows:

Measure = IF(ISINSCOPE('Table'[ID]),[mea-Mode],MAX([Column]))

Measure2 = IF(ISINSCOPE('Table'[ID]),[mea-Mode],MAX([count]))

The final output is shown below:

If it's not right, can you share with me .pbix without any sesentive information .
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[awalsh]

Thank you - should I keep the count and Column Dax the same or do I need to modify those as well? 

[parry2k]

@awalsh share sample data and expected output. Not fully clear what you are trying to achieve. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[awalsh]

Hi, 

I'm hesitant to post a sample file due to HIPAA and the nature of the data I'm working with. However, here is a screenshot with sample member ID's to help explain what I am trying to achieve. 

First, I want to have Power BI calculate the mode based on the "Length of Stay in Months" column for each month.

As you can see, for January - the formula I'm currently using worked in that it recognized that two Member IDs have a length of stay of 18 months thus, making 18 the mode for January. 

However, looking at February and so on, there is no mode because each Length of Stay only appears once.

The formula seems to be choosing the smallest Legnth of Stay for months with no mode. For example, Power BI is saying that February has a mode of 8 however, there is actually no mode that month and 8 is just the smallest Legnth of Stay for that month.  

My 2 questions are as follows: 

1. Is there any formula that can list "N/A" or something to this effect for months where there is no mode? 

2. For months where there is a mode, can I have Power BI calculate the number of Member ID's that make up that Mode? For example, January has a mode of 18 consisting of 2 member ID's. 

Thank you for your assistance.