Calculating total with displayed values

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.

total of positive results.pbix (40.6 KB) total of positive results.xlsx (8.9 KB)

Thank you.
Regards,
Aye

Hi,

This is addressed by SAM. You need to look at the formula pattern.

1 Like

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
)

2 Likes

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.
image
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