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

[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