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
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.
Solved! Go to 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.
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
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
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()
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |