How to handle multiple denominator logic for status percentage calculations in Power BI report?

[manoj_0911]

Hi Experts,

I need some help designing a clean DAX or model approach for a report that has different denominator logic per status type.

I’m working on an Agent Status Report (Power BI) that currently uses the following formulas:

LOGIN TIME       = [OFF_QUEUE] + [ON_QUEUE]
STAFFED TIME     = [LOGIN TIME] - ([BREAK] + [MEAL])

AVAILABLE %      = [AVAILABLE] / [STAFFED TIME]
AWAY %           = [AWAY] / [STAFFED TIME]
BUSY %           = [BUSY] / [STAFFED TIME]
MEETING %        = [MEETING] / [STAFFED TIME]
TRAINING %       = [TRAINING] / [STAFFED TIME]
SYSTEM AWAY %    = [SYSTEM_AWAY] / [STAFFED TIME]

BREAK %          = [BREAK] / [LOGIN TIME]
MEAL %           = [MEAL] / [LOGIN TIME]
OFF QUEUE %      = [OFF_QUEUE] / [LOGIN TIME]
ON QUEUE %       = [ON_QUEUE] / [LOGIN TIME]

IDLE %           = [IDLE] / ([IDLE] + [INTERACTING] + [NOT RESPONDING])
INTERACTING %    = [INTERACTING] / ([IDLE] + [INTERACTING] + [NOT RESPONDING])
NOT RESPONDING % = [NOT_RESPONDING] / ([IDLE] + [INTERACTING] + [NOT RESPONDING])

So basically, each status group has a different denominator:

Status Type                                                                                    Denominator

AVAILABLE, AWAY, BUSY, MEETING, TRAINING, SYSTEM_AWAY	STAFFED TIME
BREAK, MEAL, OFF_QUEUE, ON_QUEUE	LOGIN TIME
IDLE, INTERACTING, NOT_RESPONDING	SUM of (IDLE + INTERACTING + NOT RESPONDING)

---

In the Agent Status Percent Details page, this works correctly because I can calculate per category.
But in the Secondary Status Details page, the client now wants to show everything together —
and the totals and percentages become misleading since denominators differ.

My manager’s suggestion is:

“Since each status has a different denominator, check whether we can show the statuses separately in different sheets.”

---

💡What I’m trying to find out

1. Is there a better way to handle multiple denominator logic within one visual (for example, dynamically switch the denominator based on the status)?

2. Or is it really best practice to split these statuses into separate report pages (Staffed Time–based, Login Time–based, Interaction–based)?

3. If a single page is possible — what’s the most efficient DAX pattern to dynamically pick the correct denominator for each status?

---

🧱 Example

For one agent (Login Time = 42517 sec, no breaks):

• Status “Spec Proj” duration = 6353 sec
  Expected % (using Staffed Time): (6353 / 42517) * 100 = 14.9%
  But current report shows 4.93% (because it divides by total of all statuses).

---

Any guidance or DAX pattern suggestions from the community would be much appreciated 🙏

Thanks,
Manoj Prabhakar

[v-echaithra]

Hi @manoj_0911 ,

Thank you for your inquiry. I understand you need a solution to handle multiple denominator logic for status percentage calculations in your Agent Status Report, where different status types require different denominators (e.g., STAFFED TIME, LOGIN TIME, etc.). Below, I’ve outlined an efficient step-by-step approach to achieve this, ensuring your percentages are calculated accurately, even in a consolidated report view.

Step 1: Create a Denominator Classification Table
To start, we need to classify each status type into a specific group based on the denominator it should use. This classification will help us dynamically assign the correct denominator to each status.

Step 2: Define Measures for Each Status Category
The next step is to define the time-based measures that will serve as the denominators for your percentage calculations.

Step 3: Dynamic Denominator Logic
The next step is to create a dynamic measure that will select the correct denominator based on the status group of each row in the report. Using the relationship between your status table and the denominator classification table, we can dynamically switch the denominator in the formula.

Step 4: Calculate Percentage for Each Status
Once we have the dynamic denominator, the next step is to calculate the percentage for each status based on its duration and the appropriate denominator. For example, this is done using the following DAX formula:

Status Percentage (Total Safe) =
VAR Numerator = [Duration]
VAR Denominator = [Dynamic Denominator]
VAR ThisPct = DIVIDE(Numerator, Denominator)
RETURN
IF (
HASONEVALUE(StatusGroup[StatusName]),
ThisPct,
AVERAGEX(
VALUES(StatusGroup[StatusName]),
[Status Percentage]
))

Hope this helps.
Chaithra E.

[v-echaithra]

Hi @manoj_0911 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[parry2k]

@manoj_0911 could you provide little more context and I will look into it. Thanks!

[manoj_0911]

I need some help designing a clean and efficient approach for handling different denominator logic per status type in an Agent Status Report (Power BI).

Currently, my report calculates agent time metrics as follows:

LOGIN TIME       = [OFF_QUEUE] + [ON_QUEUE]
STAFFED TIME     = [LOGIN TIME] - ([BREAK] + [MEAL])

AVAILABLE %      = [AVAILABLE] / [STAFFED TIME]
AWAY %           = [AWAY] / [STAFFED TIME]
BUSY %           = [BUSY] / [STAFFED TIME]
MEETING %        = [MEETING] / [STAFFED TIME]
TRAINING %       = [TRAINING] / [STAFFED TIME]
SYSTEM AWAY %    = [SYSTEM_AWAY] / [STAFFED TIME]

BREAK %          = [BREAK] / [LOGIN TIME]
MEAL %           = [MEAL] / [LOGIN TIME]
OFF QUEUE %      = [OFF_QUEUE] / [LOGIN TIME]
ON QUEUE %       = [ON_QUEUE] / [LOGIN TIME]

IDLE %           = [IDLE] / ([IDLE] + [INTERACTING] + [NOT RESPONDING])
INTERACTING %    = [INTERACTING] / ([IDLE] + [INTERACTING] + [NOT RESPONDING])
NOT RESPONDING % = [NOT_RESPONDING] / ([IDLE] + [INTERACTING] + [NOT RESPONDING])

So basically, each status group uses a different denominator, summarized below:

Status Type Denominator

AVAILABLE, AWAY, BUSY, MEETING, TRAINING, SYSTEM_AWAY	STAFFED TIME
BREAK, MEAL, OFF_QUEUE, ON_QUEUE	LOGIN TIME
IDLE, INTERACTING, NOT_RESPONDING	IDLE + INTERACTING + NOT RESPONDING

This works correctly in category-wise visuals (like Staffed or Login-based pages).
However, the client now wants to show all secondary statuses together in one combined table —
and the percentages become misleading since the denominators differ by status group.

---

💡 What I’m Trying to Figure Out

Is there a DAX or technique that can dynamically switch the denominator based on the status name or group —
so that I can display all statuses in a single table (secondary status details table )correctly?

Or, from a best-practice point of view, is it better to separate these into different report pages
(e.g., Staffed-Time based, Login-Time based, Interaction-based)?

If a single-page solution is possible, what’s the DAX approach for handling such conditional denominator logic?

Thanks in advance!
@parry2k — tagging you since your earlier solution for independent agent-level percentages worked beautifully 🙏

[manoj_0911]

@parry2k Hi , Need help, could you please check this

[manoj_0911]

@parry2k  could you please help me here, instead of showing as 3 separate sheets , is it possible to implement the logic in a single sheet eventhough the denominators change for each status. statuse's. https://drive.google.com/file/d/1p8j2dhAoZJRHRAHVkAzEUmL2JaeKDkV_/view?usp=sharing

[v-echaithra]

Hi @manoj_0911 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, and also provide the sample data. we are happy to help.

Thank you.

[GrowthNatives]

Hi @manoj_0911 ,
Yes  it’s possible, with a model-aware DAX pattern that uses group classification and virtual tables
You can follow these steps to get the desired result 
1.  You need to classify each status into a Denominator Group once — not repeat logic in every measure.

Create a table (manually or in Power Query)

Then create a relationship between your FactAgentStatus[StatusName] → StatusGroup[StatusName].

2. Create measures 

DAX
[LOGIN TIME] =
[OFF_QUEUE] + [ON_QUEUE]

[STAFFED TIME] =
[LOGIN TIME] - ([BREAK] + [MEAL])

[INTERACTION TIME] =
[IDLE] + [INTERACTING] + [NOT_RESPONDING]

Each of [BREAK], [MEAL], [AVAILABLE], etc. should be measures returning the sum of duration per status.

3. Dynamic Denominator 

DAX 
Dynamic Denominator :=
VAR DenomGroup = SELECTEDVALUE ( StatusGroup[DenominatorGroup] )
RETURN
    SWITCH (
        TRUE(),
        DenomGroup = "STAFFED",       [STAFFED TIME],
        DenomGroup = "LOGIN",         [LOGIN TIME],
        DenomGroup = "INTERACTION",   [INTERACTION TIME],
        BLANK()
    )

4. Universal % Formula 

DAX 
Status % :=
VAR Numerator = [Duration]   -- or whatever measure gives total seconds per status
VAR Denominator = [Dynamic Denominator]
RETURN
DIVIDE ( Numerator, Denominator )

5. Fix Totals

DAX
Status % (Total Safe) :=
VAR Numerator = [Duration]
VAR Denominator = [Dynamic Denominator]
VAR ThisPct = DIVIDE ( Numerator, Denominator )

RETURN
IF (
    HASONEVALUE ( StatusGroup[StatusName] ),
    ThisPct,
    AVERAGEX (
        VALUES ( StatusGroup[StatusName] ),
        [Status %]
    )
)

This averages (or sums) the individual percentages correctly, depending on how you want totals displayed.


⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀  [Explore More]

[manoj_0911]

Could u please provide the workbook if you have tried this, it would be greatly helpful for me

[v-echaithra]

Hi  @manoj_0911 ,

If possible, could you please provide more details about your data?

How to provide sample data in the Power BI Forum

You can refer the following link to upload the file to the community.
How to upload PBI in Community

Thank you.

[manoj_0911]

Getting this error while trying this