Rank based on revenue then use parameter to show as Others or Customer based on that parameter

[CrystalGail84]

Hi Happy Friday Eve Everyone!

I am very new to Power BI and have searched for hours to figure out my issue.

My data includes customer name and transactional revenue.  I added a rank column:

Rank = RANKX(FILTER(ALL('DW RevenueData'[Shipping Global Customer]),LEFT('DW RevenueData'[Shipping Global Customer],11)<>"No Customer"),CALCULATE(SUM('DW RevenueData'[USD Amount]),ALLEXCEPT('DW RevenueData','DW RevenueData'[Shipping Global Customer])),,DESC)

Next I added a Parameter 5 to 50, increments of 5 called Filtered Rank:

Filtered Rank = GENERATESERIES(5, 50, 5)

Filtered Rank Value = SELECTEDVALUE('Filtered Rank'[Filtered Rank])

Finally I created a calculated column to use the filtered rank value to change the Shipping Global Customer Name to "Others" when it was greater than the filtered rank value:

Filtered Rank Shipping Global Customer = IF(OR('DW RevenueData'[Rank]>'Filtered Rank'[Filtered Rank Value],LEFT('DW RevenueData'[Shipping Global Customer],11)="No Customer"),"Others",'DW RevenueData'[Shipping Global Customer])

This works as expected when I use a value like 10 instead of the Filtered Rank Value:

Filtered Rank Shipping Global Customer = IF(OR('DW RevenueData'[Rank]>10,LEFT('DW RevenueData'[Shipping Global Customer],11)="No Customer"),"Others",'DW RevenueData'[Shipping Global Customer])

Any help you guys can provide is most appreciated!

Crystal

[johnt75]

Calculated columns are only calculated during data refresh, so they do not pay any attention to slicers or filters you may have in a report. If you want something to react dynamically you will need a measure.

You could create a measure like

Filtered Rank Shipping Global Customer Measure =
VAR CustomerName =
    SELECTEDVALUE ( 'DW RevenueData'[Shipping Global Customer] )
VAR CustomerRank =
    SELECTEDVALUE ( 'DW RevenueData'[Rank] )
VAR Result =
    IF (
        OR (
            CustomerRank > 'Filtered Rank'[Filtered Rank Value],
            LEFT ( CustomerName, 11 ) = "No Customer"
        ),
        "Others",
        CustomerName
    )
RETURN
    Result

but you will need to put in the visual a column from the revenue table that uniquely identifies a row.

[CrystalGail84]

I brought in some co-workers to help me and we are having a problem when trying to show the data.  So previous I had Rank now it is called Rank Column.

Using your suggestion: 

Filtered Rank Shipping Global Customer Measure = 
VAR CustomerName =
    SELECTEDVALUE ( 'DW RevenueData'[Shipping Global Customer] )
VAR CustomerRank =
    SELECTEDVALUE ( 'DW RevenueData'[Rank Column] )
VAR Result =
    IF (
        OR (
            CustomerRank > 'Filtered Rank'[Filtered Rank Value],
            LEFT ( CustomerName, 11 ) = "No Customer"
        ),
        "Others",
        CustomerName
    )
RETURN
    Result

Both with thier measures and the one you gave me, this happens:

We are switching from Tableau to Power BI.  This is what I am looking to ceate:

I am getting closer when I make a small change to your suggestion to use Top N Customer instead of their name. 

Filtered Rank Shipping Global Customer Measure = 
VAR CustomerName =
    SELECTEDVALUE ( 'DW RevenueData'[Shipping Global Customer] )
VAR CustomerRank =
    SELECTEDVALUE ( 'DW RevenueData'[Rank Column] )
VAR Result =
    IF (
        OR (
            CustomerRank > 'Filtered Rank'[Filtered Rank Value],
            LEFT ( CustomerName, 11 ) = "No Customer"
        ),
        "Others",
        "Top "&'Filtered Rank'[Filtered Rank Value]&" Customers"
    )
RETURN
    Result

Now the issue is I can't move this to a row like my Tableau report:

Is this because it is a measure?  Any ideas how to fix this?

[johnt75]

You could try putting the actual customer name on the rows of a matrix and put this measure as the first value. Then set the width of the customer name to 0. You may need to turn off word wrap for that column.