Dynamic Calculation based on filtering

[Anonymous]

Hello,

New to power bi, I need Power Bi to recalculate percentages based on the filters of a visualization.

I need to divide the Planned hours by actual hours to get an efficiency %, and then to be able to recalculate by whatever filter I choose i.e company, location, or week. Right now it is currently summing all planned hours and actual hours regardless of the filter.

data

an attempt at the formula:

Efficiency = CALCULATE(sum(table[planned hours)/sum(table[actual hours]),ALLSELECTED(table))

[Anonymous · Accepted solution]

@Anonymous 

You cannot divide in the calculate function, try separate the expression like the following:

Efficiency = CALCULATE(sum(table[planned hours),ALLSELECTED(table)) / CALCULATE(sum(table[actual hours]),ALLSELECTED(table))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

I'm not clear on why you are using ALLSELECTED. You are telling it to remove the filters from the table internally but keep filters from the outside. That means every row will be the same for whatever slicers/filters/crosshighlighting happens, but the filter context within the filter (company rows) is ignored. Just use this.

Effeciency = 
    DIVIDE(
        SUM('Table'[Planned]),
        SUM('Table'[Actual])
    )

You would only want some sort of ALL/REMOVEFILTERS if you needed the denominator for example, to remain constant no matter what, and then you use it on the denominator only. You've effectively used it on both numerator and denominator.

If you need more help, please post some data we can use (links below) and your expected results. Screenshots from Excel are good for expected result mock ups, but not for providing data for us to use.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum