Dividing the sum of two columns with a filter

[BrianPansy]

I'm trying to do a simple rate calcation, summing an entire column (my numerator) then dividing by the sum an another entire column (my denominator), filter by metric. I have 3 companies in my database, but the formula below keeps returning the rate by company, not aggregating num/den across all 3 companies.

ALL_CurrentEngagment = 
           CALCULATE(
                DIVIDE(SUM(STP_OPS_KPI[NUM]),SUM(STP_OPS_KPI[DEN])),
                FILTER(STP_OPS_KPI, STP_OPS_KPI[METRIC]="Current Engaged Population"))

What am I doing wrong? This should be simple yet I'm struggling.

[Ashish_Mathur]

Hi,

The ALL() function will have to be used.  Share the download link of the PBI file.  Show the problem and the expected result.

[Royel · Accepted solution]

Hi @BrianPansy  lets simplified the dax 

OptimizedTest = 
VAR AllCompanyNum = 
    CALCULATE(
        SUM(STP_OPS_KPI[NUM]),
        REMOVEFILTERS(STP_OPS_KPI[COMPANY]),
        STP_OPS_KPI[METRIC] = "Current Engaged Population"
    )
VAR AllCompanyDen = 
    CALCULATE(
        SUM(STP_OPS_KPI[DEN]),
        REMOVEFILTERS(STP_OPS_KPI[COMPANY]),
        STP_OPS_KPI[METRIC] = "Current Engaged Population"
    )
RETURN DIVIDE(AllCompanyNum, AllCompanyDen)

Test each variables are working perfectly or not. If it works then try this somplified version 

OptimizedRate =
CALCULATE(
    DIVIDE(SUM(STP_OPS_KPI[NUM]), SUM(STP_OPS_KPI[DEN])),
    REMOVEFILTERS(STP_OPS_KPI[COMPANY]),
    STP_OPS_KPI[METRIC] = "Current Engaged Population"
)

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[BrianPansy]

Optimize Rate worked perfectly. Thanks a million!

[BrianPansy]

TY for the prompt reply. I had that at one point in my stuggles.  That works but it doesn't break things out monthly. How do I incorporate that into the formula?

[Greg_Deckler]

@BrianPansy Maybe:

ALL_CurrentEngagment = 
           CALCULATE(
                DIVIDE(SUM(STP_OPS_KPI[NUM]),SUM(STP_OPS_KPI[DEN])),
                FILTER( ALL(STP_OPS_KPI), STP_OPS_KPI[METRIC]="Current Engaged Population"))