Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

If Statement applied at a certain aggregated level

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"

)

General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    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

3 Replies

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous , 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous ,

        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