Total bed per day from daily available beds

DAX DAX Calculations
1

Hi Team,
I tried calculating Total available beds by using following DAX.
test =
DIVIDE(
CALCULATE(SUM(Data[Stats Data]), Data[Act/Bud]= “Act”,Data[Category] = “Available Bed”),
CALCULATE(COUNT(Data[FP]), Data[Act/Bud]= “Act”,Data[Category] = “Available Bed”))

Bed day Per Facility is correct but the total is the average of the total bed. 188 is incorrect. I would like to get 564.
Please see attached and can you please let me know correct DAX.
Many thanks.

Regards,
Ammu
TotalBedDays.pbix (51.9 KB)

2

Hello @ammu,

Thank you for posting your query onto the Forum.

I’m attaching the screenshot as well as the PBIX file of the working for the reference.

Hoping you find this useful and helps you to achieve the desired analysis.

Thanks & Warm Regards,
Harsh

Average Beds Total

TotalBedDays.pbix (56.6 KB)

1 Like
3

@ammu,

Try this:

Test2 = 

VAR AvgBeds = 
DIVIDE(
    CALCULATE (
        SUM ( Data[Stats Data] ),
        Data[Act/Bud] = "Act",
        Data[Category] = "Available Bed"
    ),
    CALCULATE (
        COUNT ( Data[FP] ),
        Data[Act/Bud] = "Act",
        Data[Category] = "Available Bed"
    )
)

VAR vTable =
ADDCOLUMNS(
    SUMMARIZE(
        Data,
        Data[Facility]
    ),
    "@Average Beds", AvgBeds
)

VAR Result =
IF( HASONEVALUE( Data[Facility] ),
    AvgBeds,
    SUMX( 
        vTable,
        [@Average Beds]
    )
)

RETURN
Result

image

This revised measure uses your calculation as the foundation, but then builds a simple 2-column virtual table, composed of facility in one column and your measure in the second column. It then checks via HASONEVALUE whether we’re in a data row or a total row. In the former case it returns your measure, in the latter case it returns a total via SUMX of the second column of the virtual table.

I hope this is helpful. Full solution file posted below.

4

Endlessly fascinating. Same problem, same tools, same end result, but two completely different approaches. @Harsh - I’ve seen a ton of different approaches to forcing the total (HASONEVALUE, ISFILTERED, ISINSCOPE, nesting within SUMX, etc.) but this is the first time I’ve seen someone do it with SWITCH(TRUE()). Very creative approach.

  • Brian
1 Like
5

Alternatively you could also use DISTINCTCOUNT instead of COUNT

test =
DIVIDE(
CALCULATE(SUM(Data[Stats Data]), Data[Act/Bud]= "Act",Data[Category] = "Available Bed"),
CALCULATE(DISTINCTCOUNT(Data[FP]), Data[Act/Bud]= "Act",Data[Category] = "Available Bed"))

With this result
image

I hope this is helpful.

2 Likes
6

Hello @BrianJ,

Thank you for your kind words and recognizing my efforts. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

7

Many thanks, Harsh.
I have to keep your formula for future reference.
It is going to be such a useful one.

Regards,
Ammu

8

Many thanks, Brian.
This is also a great one.
It is fascinating for beginner DAX learner like me.
Greatly appreciated.
Regards,
Ammu

9

Hi Melissa,
It is very simple and easy. Thank you so much for your kind help.
I am so grateful for your post.

Regards,
Ammu

10

Hi @ammu, glad you made a progress with your query, 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!

https://analysthub.enterprisedna.co/dax-clean-up