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

[CrystalGail84 · Accepted solution]

None of the options worked and I handed over the project to someone else on my team

[v-csrikanth]

Hi @CrystalGail84 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @CrystalGail84 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @CrystalGail84 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @johnt75 , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

Best Regards,
Cheri Srikanth

[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.