Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
awalsh
Helper I
Helper I

Mode and Count of Items for Mode

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?)

 

awalsh_3-1626278431077.png

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. 

 

 

1 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

 

vxiaotang_2-1626846782417.png

 

 

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.

View solution in original post

6 REPLIES 6
v-xiaotang
Community Support
Community Support

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
Community Support
Community Support

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.

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. 

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

 

vxiaotang_2-1626846782417.png

 

 

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.

@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!!!! 

Hi @awalsh 

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

vxiaotang_0-1627630193910.gif

 

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.