If Statement applied at a certain aggregated level

[plin924]

Hi,

I need help with DAX.  

I have a table that has a varius level of aggregate.  (Region, Office, Sales Person).

How do I write the following measures?
1) Aggregate Numerator and Denominator at Office level.

2) apply an if statement, also at office level?   
switch(true(),
divide(office level numerator, office level denominator,0)>40%,"Bad"
divide(office level numerator, office level denominator,0)>60%,"Average"
divide(office level numerator, office level denominator0)>80%,"Good"
divide(office level numerator, office level denominator,0)>100%,"Perfect"

)

[Jai-Rathinavel]

Hi @plin924 , You can write a DAX measure like below

Office Level % =
var result = CALCULATE(DIVIDE(SUM(Table[Numerator]),SUM(Table[Denominator]),0),ALLEXCEPT(Table[Office]))
RETURN
SWITCH(
        TRUE(),
        result <= 0.4, "Bad",
        result > 0.4 && result <= 0.6,"Average",
        result > 0.6 && result <= 0.8,"Good",
        result > 0.8 && result <= 1,"Perfect")

Did I answer your question ? If yes, please mark this post as a solution

Thanks,

Jai

[plin924]

sorry.. it didn't quite work. I was hoping it will apply criteria first, then sum. It didn't sum properly.

[Anonymous · Accepted solution]

Hi @plin924 ,

You can create two measures as below to get it, please find the details in the attachment.

Percent = DIVIDE(SUM('Table'[Numerator]),SUM('Table'[Denominator])) 

Office level = 
VAR _sumofper =
    SUMX ( ALLEXCEPT ( 'Table', 'Table'[Office] ), [Percent] )
RETURN
    SWITCH (
        TRUE (),
        _sumofper > 1, "Perfect",
        _sumofper > 0.8
            && _sumofper <= 1, "Good",
        _sumofper > 0.6
            && _sumofper <= 0.8, "Average",
        _sumofper <= 0.6, "Bad"
    )

Best Regards