Retaining value in a row but excluding it from Total in Matrix visualization

[Diana2022]

Hello, thank you in advance for any insights.  My forum search didn't result anything identical to this issue.

I have a Matrix visualization.  Each row is a department.  The column is an efficiency measure : 1/(SUM(Worked Hrs)/SUM(Earned Hrs))

Worked Hours and Earned hours come from a table in PowerQuery.

But there are a few departments, like Maintenance, that use the Total of all other departments as its Earned Hours.  E.g., departments A+B+C=Total and Maintenance =Total.   If Maintenance Earned Hours is included into Total then the visualization has twice the number of actual Earned Hours.  

My efficiency Total is five times what it should be because there are four departments like Maintenance...but each row is accurate.

Is there a way to keep Earned Hours so these four departments can see their efficiency but exclude those hours from the matrix Total? 

Thank you!

@selimovd 

[v-henryk-mstf]

Hi @Diana2022 ,

Try the measure formula as below:

1 =
IF (
    HASONEFILTER ( department_column ),
    1
        / ( SUM ( WorkedHrs ) / SUM ( EarnedHrs ) ),
    CALCULATE (
        1
            / ( SUM ( WorkedHrs ) / SUM ( EarnedHrs ) ),
        department = "Maintenance"
    )
)

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your feedback.

Best Regards,
Henry

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

[coskuersanli]

Hi Diana,

You can use HASONEFILTER() and filter total line. Can you try below measure?

IF(HASONEFILTER(department_column),  1/(SUM(Worked Hrs)/SUM(Earned Hrs)) ,  1/(SUM(Worked Hrs)/SUM(Earned Hrs)) - Maintenance)

[Diana2022]

hi! Thank you for introducing me to this...its not quite working for me but I'm working on understanding it in the documentation:)

[coskuersanli]

Hi Diana,

Would it be a solution to use total for maintenance? Can you check below measure please?

IF(HASONEFILTER(department_column),  1/(SUM(Worked Hrs)/SUM(Earned Hrs)) ,  CALCULATE(1/(SUM(Worked Hrs)/SUM(Earned Hrs)), department = "Maintenance")