Topn bottom is blank

DAX DAX Calculations
1

Hello all,

I’m using the topn advice from the EDNA video to build a card showing a top performing group and bottom group. My top is fine, but my bottom (just changing is to asc) is bringing in blanks. For reference, I pulled a table in with the information thinking I would see blank fields, but I don’t see any from a filter standpoint. Any help would be greatly appreciated.

Bottom Ranked Area = CALCULATE(SELECTEDVALUE(DEPARTMENT[DESCRIPTION]),TOPN(1,ALL(DEPARTMENT[DESCRIPTION]), [Average Utilization],ASC))

summary
summary1297×415 29 KB

2

i don’t know exactly the model, but if you really have one blank row in your model and don’t want to show it. should considerer to use the ALLNOBLANKROW()

Bottom Ranked Area = CALCULATE(SELECTEDVALUE(DEPARTMENT[DESCRIPTION]),TOPN(1,ALLNOBLANKROW(DEPARTMENT[DESCRIPTION]), [Average Utilization],ASC))

3

Thanks. I added that but it is still blank.

4

maybe not using the selectedvalue() , since you are not selecting anything… use VALUES() insted
i rather use:

Bottom Ranked Area =
CALCULATE (
    VALUES ( DEPARTMENT[DESCRIPTION] ),
    TOPN (
        1,
        ALLNOBLANKROW ( DEPARTMENT[DESCRIPTION] ),
        [Average Utilization], ASC
    )
)

if you have a sample would be easier hehhehee because i’m trying to guess the model and the measure, but i think it could work.

take in consideration that if your measure [ Average Utilization] just generate blanks() due to context, everything always will be blank()

5

Thanks Andre. Got this error on that latest recommendation

Error Message:
MdxScript(Model) (15, 54) Calculation error in measure ‘Measure Table’[Bottom Ranked Area]: A table of multiple values was supplied where a single value was expected.

How can I get a sample to you without the security risk? Thanks!

6

Hi James,
the error you’re getting is becouse multiples results are in the topn function…
it may be because there is a tie or because the measure is generating only blank results.

i would try to create a table or use dax studio to check the topn result
you may try to create a table in dax, and get only the

TOPN (
        1,
        ALLNOBLANKROW ( DEPARTMENT[DESCRIPTION] ),
        [Average Utilization], ASC
    )

and check the result, if it is bringing lots of results (because was a tie) or if it will bring only blank results…

if bring blank() results, i would try to change the measure to see if it is working ( test with a [amount] or same simples measure that do not play with context)

about the file, i htink if you post the [Average Utilization] measure it will help, because i’m really thinking the problem is there.

If is a Tie, then you could change this to use a Lastnonblank() , like

CALCULATE (
    LastNonBlank( DEPARTMENT[DESCRIPTION] , 1 ),
    TOPN (
        1,
        ALLNOBLANKROW ( DEPARTMENT[DESCRIPTION] ),
        [Average Utilization], ASC
    )
)
7

Great progress here, Andre!
So I created the table and there were 43 results and no blanks. What may be happening is that some of these results will never have a utilization percentage (due to not being direct labor)

here is the measure for Average Utilization and the embedded Utilization measure:

Average Utilization = AVERAGEX(VALUES(‘Date Table’[Date]),[Utilization])

Utilization = DIVIDE(SUMX(LABOR_TICKET,LABOR_TICKET[HOURS_WORKED]),SUMX(ADP,ADP[Total Hours]))

Lastly, adding your last formula brought back a department! It’s not the lowest-ranked department but one that doesn’t have any labor on it. It’s getting closer!! I’ll do more digging.

Annotation 2020-04-22 172318
Annotation 2020-04-22 172318967×388 23.6 KB

8

Hi @JamesT, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!