Forum Discussion
IF Function
Hello,
I'm still learning all of this DAX and Power BI function writing. I am trying to get the below to break out a column into 3 levels? It's only picking up the GOLD level.
Unit Level = IF(SUM([Avg. Units])<9,"Blue",IF(SUM([Avg. Units])>=18,"Gold","Silver"))
| Originator | Total Units | Total Volume | Avg. Units | Avg. Volume | Unit Level |
| Spangler, Brady | 62 | $26,621,485 | 20.66666667 | $8,873,828 | Gold |
| Hennessy, Matt | 55 | $12,639,677 | 18.33333333 | $4,213,226 | Gold |
| Wickett, Michelle | 46 | $10,607,464 | 15.33333333 | $3,535,821 | Gold |
| Smukalla, TC | 28 | $9,190,567 | 9.333333333 | $3,063,522 | Gold |
| Sparks, Suzie | 19 | $8,625,007 | 6.333333333 | $2,875,002 | Gold |
| Shtatman, Matthew | 32 | $8,512,157 | 10.66666667 | $2,837,386 | Gold |
| Hobson, Richard | 18 | $8,446,110 | 6 | $2,815,370 | Gold |
| Meyerink, Nicole | 25 | $7,390,523 | 8.333333333 | $2,463,508 | Gold |
| Tschernia, Paul | 28 | $7,303,790 | 9.333333333 | $2,434,597 | Gold |
| Julien, Amy | 30 | $6,798,516 | 10 | $2,266,172 | Gold |
| Laughlin, Patrick | 18 | $6,171,351 | 6 | $2,057,117 | Gold |
| Eaton, Julia | 14 | $6,161,585 | 4.666666667 | $2,053,862 | Gold |
| Robertson, Rick | 13 | $6,013,690 | 4.333333333 | $2,004,563 | Gold |
| Walsh, Michael | 24 | $5,822,771 | 8 | $1,940,924 | Gold |
My Avg Units Column is the sum of a calculated column so not sure if that is my hang up
Avg. Units = CALCULATE(DISTINCTCOUNT('Last Quarter'[Loan Number])/3)
15 Replies
Hey,
I assume that Unit Level is also a calculated column.
Put the expression inside a CALCULATE( ... ) this should solve your problem.
Using SUM(...) means always aggregate the available rows of the table considering the current Filter Context.
Definig a calculated column one has to consider that there is no Filter Context, just a Row Context. This means using SUM(...) aggregates all the rows, for this reason you just have "Gold".
Two possibilities
- Wrap CALCULATE around the expression. CALCULATE( ) introduces a Filter Context. Using CALCULATE() inside a Row Context means, the Filter Context is just the current row, this of course can be changed by adding more than just the 1st parameter - the expression.
- Remove the SUM()s from your expression, to reference a value from a certain column inside a Row Context it's not necessary to use SUM
Hope this helps
Hey,
this will resolve the error message
CALCULATE(
< your expression >,ALLEXCEPT('yourtablename', 'yourtablename'[name of the calculated column 1])
)Regards Tom
