Hi Team,
I tried calculating total for displayed values only.
I have modified column for no. of bed to achieve for budgeted occupancy.
The highlighted result in my visual should be 21 instead of 2.
can you please let me know how to fix them.
Thank you.
Regards,
Aye
Hi,
This is addressed by SAM. You need to look at the formula pattern.
Hi @ammu,
You can try this measure
Modified No. of Bed to Bud Occ% =
IF (
[No. of Bed to Bud Occ%] > 1,
SUMX (
FILTER (
Sheet1,
[No. of Bed to Bud Occ%] > 1
),
[No. of Bed to Bud Occ%]
),
0
)
Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!
Many thanks, Harsh and Piryani. I am much appreciated.
I have one more isssue as follows:
the one I do not have set the budget for (because that cost center is now closed ) has NaN in the result. that is ok. But how can I still get total ignoring NaN. thank you.
Best regards.
ammu
Hi @ammu,
You generally get a NaN error when you Divide 0 by 0.
So in this case,
Try something like this.
Measure =
If [no of Res] = 0 || [NY Bed Occ] = 0 || MAX('Table' [CostCenter]) = BLANK() , 0 , DIVIDE(what you are dividing) )
You can also have a look these links.
https://www.spguides.com/power-bi-nan-error/
https://radacad.com/make-your-numeric-division-faultless-in-power-query
Regards,
Harsh Nathani
Many thanks, Harsh.
It is working for me .
Regards,
Aye