topic Re: Need Help for Dax formula to summarize and get vmax and average value for each categories in column in DAX Commands and Tips

Need Help for Dax formula to summarize and get vmax and average value for each categories in column

Eugine — Mon, 12 Feb 2024 03:56:22 GMT

Hi Team,

I'm a beginner in using powerbi as well as qriting dax formula and hoping to ge help.
I am currently working on a table and tring to write a formula to get the max and average value og "Occupied", "Unoccupied" and dthe combined data for "Occupied" and "Signs of Life" to use as a score card. My table is laid out row=day, time, weekday and column = status. and example, say when I filter day=4 then the max value that should show from the score card will be 32.96% but if no filter is selected it will display the max/ ave value from all entries accross all dates. Thanks!

Re: Need Help for Dax formula to summarize and get vmax and average value for each categories in column

123abc — Mon, 12 Feb 2024 04:39:53 GMT

To achieve this in Power BI using DAX formulas, you can create calculated measures for the maximum and average values of the "Occupied", "Unoccupied", and combined "Occupied" and "Signs of Life" statuses. Here's how you can do it:

Assuming you have a table named YourTableName with columns: Day, Time, Weekday, and Status, you can create calculated measures as follows:

Max Occupied:

Max Occupied = MAXX(FILTER(YourTableName, YourTableName[Status] = "Occupied"), YourTableName[Occupied])

Average Occupied:

Average Occupied = AVERAGEX(FILTER(YourTableName, YourTableName[Status] = "Occupied"), YourTableName[Occupied])

Max Unoccupied:

Max Unoccupied = MAXX(FILTER(YourTableName, YourTableName[Status] = "Unoccupied"), YourTableName[Occupied])

Average Unoccupied:

Average Unoccupied = AVERAGEX(FILTER(YourTableName, YourTableName[Status] = "Unoccupied"), YourTableName[Occupied])

Max Occupied and Signs of Life Combined:

Max Occupied and Signs of Life Combined =
MAXX(
FILTER(
YourTableName,
YourTableName[Status] = "Occupied" || YourTableName[Status] = "Signs of Life"
),
YourTableName[Occupied]
)

Average Occupied and Signs of Life Combined:

Average Occupied and Signs of Life Combined =
AVERAGEX(
FILTER(
YourTableName,
YourTableName[Status] = "Occupied" || YourTableName[Status] = "Signs of Life"
),
YourTableName[Occupied]
)

These measures will calculate the maximum and average values for each category based on the filter context you apply in Power BI visuals or reports. Make sure to replace YourTableName with the actual name of your table. Also, ensure that the column names in the formulas match your actual column names.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Need Help for Dax formula to summarize and get vmax and average value for each categories in column

Eugine — Mon, 12 Feb 2024 04:56:58 GMT

Thanks, but not sure where I missed the part but still not getting the correct expected result, I may have missed mentioning that the field i used for the column values is "Occupancy".