DAX command to show top 4/bottom 4 variances in a matrix table

[JoshBrown92]

Hi,

I'm pretty new to Power BI so I might be missing an easier way of doing this. I'm trying to show the top 4 and bottom four variances from estimated value to actual value. The only way I've been able to display this is using a matrix table and using two measures, for rank and for variance.

I'm trying to limit the number of entries shown to four each but have had no success. I found a youtube video that shows how to do it but I'm getting a 'A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.' when using it with my variables used.

From what I've read I need to use a FILTER function to fix this, but I'm not sure where. Any advice/ideas how to achieve what I'm after?

Thanks!

[Dangar332]

Hi, @JoshBrown92 

you have placehholder error 
use your measure inside of filter ()
refer below solved LINK 

[scee07]

Hi, 

as far as I understood, there should be some measure that results in a a switch to show either the bottom records or the top records. To my knowledge one can only filter by table columns in Power BI (anybody correct me, if I am wrong). Anyway let's try a different approach. 

I do some simualtion to get some toy data:

TransactionIDs =
GENERATESERIES ( 1, 50, 1 )

TransactionIDs =
GENERATESERIES ( 1, 50, 1 )

Transactions =
CROSSJOIN ( EntityIDs, TransactionIDs )

Variation =
SUMMARIZE (
    Transactions,
    Transactions[EntityID],
    "Variation", [VariationMeasure]
)

VariationMeasure =
STDEV.P ( Transactions[Value] )

This is giving me values for some entities where I want to know bottom and top. 

A calcuated column now qualifies a record as bottom or top or neither of the two.

TopOrBottom =
VAR entityID = Variation[EntityID]
VAR tops =
    FILTER (
        TOPN ( 4, ALL ( Variation ), Variation[Variation], DESC ),
        [EntityID] = entityId
    )
VAR bottoms =
    FILTER (
        TOPN ( 4, ALL ( Variation ), Variation[Variation] * -1, DESC ),
        [EntityID] = entityId
    )
VAR istop =
    COUNTROWS ( tops ) == 1
VAR isbottom =
    COUNTROWS ( bottoms ) == 1
RETURN
    IF ( isbottom, "IsBottom", IF ( istop, "IsTop", BLANK () ) )

 The TopN DAX command just gives you the top n records according to one of the table's columns

The table looks like

Now, a filter can be applied to only show the tops or bottoms (if yo don't like the blank you can do a helper filter table without the blank and filter the above table with reference to the TopOrBottom field. 

Hope this helps. 

Best regards 

Christian

[JoshBrown92]

Hi Christian,

Thanks for responding, unforutnately I need to show top + bottom at the same time in the same table, and there's also no room for slicers/checkboxes in my dashboard (it's also going to be printed so can't use the functionality).

[scee07]

Hi, 

I am not sure I am following you. All the values are already in the table with the calculated column. Never mind. If the correction of your formular below by ERD does not solve your problem, you might send an excel screenshot of what you final result should be. 

Best regards 

Christian

[ERD · Accepted solution]

Hi @JoshBrown92 , you have syntax issues and PBI is showing you where. 

VAR _Losers = FILTER(..)