Forum Discussion
undefined
Hi,
I need to find out the solution that even if we have data of output hours for given entities but if input hours are not available for some specific entities then prod efficiency should show 0 for those specific entities. Even after selecting multiple entities at same time. It should only reflect or calculate when we have both input and output hours, so that it will calculate accordingly.
6 Replies
Hi AditiGupta_ , Can you explain the problem statement with a sample data for better understanding ?
ThanksHi,
I have added screenshot please have a look, and let me know asap!!
Thank you for helping!!
Hi AditiGupta_ ,
Try to update DAX measures by :
1. Output Hours (Final): add a conditional check
Output Hours (Final) =
VAR _InputHrs = CALCULATE(SUM('Input Hrs'[Input Hours]))
VAR _OutputHrs = SUM('Warehouse PRODROUTETRANS'[Output Hours])
RETURN
IF(
NOT ISBLANK(_InputHrs) && _InputHrs > 0,
_OutputHrs,
BLANK()
)
2. Input Hours (Final): stays clean
Input Hours (Final) =
VAR _InputHrs = CALCULATE(SUM('Input Hrs'[Input Hours]))
RETURN
IF(
NOT ISBLANK(_InputHrs) && _InputHrs > 0,
_InputHrs,
BLANK()
)
3. Prod Eff: the critical fix
Prod Eff =
VAR _InputHrs = [Input Hours (Final)]
VAR _OutputHrs = [Output Hours (Final)]
RETURN
IF(
ISBLANK(_InputHrs) || _InputHrs = 0,
0, -- No input hours → show 0
DIVIDE(_OutputHrs, _InputHrs, 0)
)
4. Total Output Hours for KPI Card (only counts valid entities)
Total Output Hours (KPI) =
SUMX(
VALUES('Warehouse PRODROUTETRANS'[Entity]), -- loop per entity
VAR _InputHrs = CALCULATE(SUM('Input Hrs'[Input Hours]))
VAR _OutputHrs = CALCULATE(SUM('Warehouse PRODROUTETRANS'[Output Hours]))
RETURN
IF(
NOT ISBLANK(_InputHrs) && _InputHrs > 0,
_OutputHrs,
BLANK() -- excludes entity with no input hours from total
)
)
Hope this helps! Please, don't forget to accept as solution ✅ and thumbs up 👍 in order to keep helping others.
Best regards,
Oussama (Data Consultant - Expert Fabric & Power BI)
hi AditiGupta_
If you just need the specific entities to display 0 in a table or matrix when they lack input hours, a simple IF statement checking for blanks will do the job.
Prod Efficiency =
VAR _InputHours = [Input Hours]
VAR _OutputHours = [Output Hours]
RETURN
IF (
ISBLANK ( _InputHours ) || _InputHours == 0,
0, -- Forces a 0 if input hours are missing or exactly zero
DIVIDE ( _OutputHours, _InputHours, 0 )
)While this fixes the individual entity rows, your Grand Total might still sum up the output of the "zeroed" entities and divide it by the total inputs, which isn't always mathematically accurate for efficiency.
Approach 2:
Because you mentioned selecting multiple entities at the same time, we need to ensure the Grand Total ignores the output hours of any entity that is missing its input hours. Otherwise, your total efficiency is inaccurate.
We can achieve this by using an iterator (FILTER and VALUES) to create a virtual table of only the valid entities before doing the math.
Prod Efficiency =
-- 1. Build a virtual table of entities in the current filter context that ACTUALLY have input hours
VAR _ValidEntities =
FILTER (
VALUES ( 'YourEntityTable'[EntityName] ),
NOT ISBLANK ( [Input Hours] ) && [Input Hours] > 0
)
-- 2. Calculate Output and Input strictly for those valid entities
VAR _ValidOutput =
CALCULATE ( [Output Hours], _ValidEntities )
VAR _ValidInput =
CALCULATE ( [Input Hours], _ValidEntities )
VAR _CurrentInput = [Input Hours]
RETURN
-- 3. If the current row has no input, return 0. Otherwise, safely divide the valid numbers.
IF (
ISBLANK ( _CurrentInput ) || _CurrentInput == 0,
0,
DIVIDE ( _ValidOutput, _ValidInput, 0 )
)If this solves your problem, mark this as solution and give me a kudos
Hi AditiGupta_,
Thank you mizan2390 oussamahaimoud and Natarajan_M for your response to the query.
Does the issue occur only at the individual entity level, or is the problem mainly with the Grand Total/KPI when multiple entities are selected? If possible, please share a small sample of the Input Hours and Output Hours data along with the current DAX measure. This will help determine whether a simple blank check is sufficient or whether the calculation needs to exclude entities without Input Hours from the total aggregation.
Thank you.
Hi AditiGupta_,
Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.
If you have any further questions, feel free to reach out anytime.
Thank you.
