Mode and Count of Items for Mode

[awalsh]

Hi, 

I am fairly new to Power BI and am trying to figure out if it's possible to: 

1. Have the Mode calculated based on the Legnth of Stay column for each month? (example: for September the Mode would be 11). 

2. Count the number of Member ID's that make-up each mode? (example: to show that there are a count of 4 member ID's that make-up the mode for September).

3. If there is no mode, can Power BI display N/A for that month? (example: October has no mode, can power BI dispaly "N/A" or something along those lines?)

I tried to upload a sample PBIX file, but there is no option for me to upload on here? I uploaded it to my google drive for people to download at this link: (once it opens, i think there should be a download button in the top right corner.) 

https://drive.google.com/file/d/1KjFcT8QuINWkmB_Uj4bfB2eHHlWuitAA/view?usp=sharing

Thank you in advance for any assistance. 

[v-xiaotang]

Hi @awalsh 

Have you solved your problem? If yes, kindly accept the answer helpful as the solution. so the others can find it more quickly.
really appreciate!
Any question, please let me know.

Best Regards,

Community Support Team _Tang

[v-xiaotang]

Hi @awalsh 

the first question, "Have the Mode calculated based on the Legnth of Stay column for each month? "

- how we calculate Mode? September, Mode=11; October, Mode=?; ...

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[awalsh]

October doesn't have a mode - the mode is the most common number for each month. September it's 11, October doesn't have a mode, which is what my question #3 was about. 

[v-xiaotang · Accepted solution]

thanks for your reply @awalsh 

I got it.  if you want to calculate Mode by DAX, here are steps you can take for reference.

-

add a column Year-Month to your table,

YM = DATE(YEAR('Table 1'[Expiration Date]),MONTH('Table 1'[Expiration Date]),1)

then, add a column frequency,

frequency = 
var _countvalue='Table 1'[Legnth of Stay (Months)]
var _value='Table 1'[YM]
return 
CALCULATE (
    COUNTROWS('Table 1'),
    FILTER (
        'Table 1',
        'Table 1'[YM]=_value&&'Table 1'[Legnth of Stay (Months)]=_countvalue
    )
)

 at the end, create the measure,

returnMode = 
var _max=CALCULATE(MAX('Table 1'[frequency]),ALLEXCEPT('Table 1','Table 1'[YM]))
var _count=CALCULATE(DISTINCTCOUNT('Table 1'[Legnth of Stay (Months)]),FILTER(ALLEXCEPT('Table 1','Table 1'[YM]),'Table 1'[frequency]=_max))
return IF(_count=1&&MIN('Table 1'[frequency])=_max,MIN('Table 1'[Legnth of Stay (Months)]),"N/A")

result

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[awalsh]

@v-xiaotang Thank you so much, this is perfect!

Is there a way to filter out the N/A's from the table so that it just shows the items that have a mode? Thank you again for your help!!!! 

[v-xiaotang]

Hi @awalsh 

no problem, you can replace N/A with blank()

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.