Forum Discussion
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 Denomin...
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
Jai-Rathinavel
Super User
Super UserHi 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
sorry.. it didn't quite work. I was hoping it will apply criteria first, then sum. It didn't sum properly.
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