Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wes-shen-poal
Helper III
Helper III

Dynamic Chart Based on Rank

Hi there,

 

I'm wanting to create a column chart that will show me the count of vehicles for TopN Consignees + Other. It is the Other i don't know how to calculate. Other refers to all the Consignees combined that is not in my TopN.

 

To guide me on how to create rank measure, and TopN filter, I referred to this article: https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/

 

With success, I've managed to get the vehicle count for my Top 10 Consignee (Consignee on my x-axis), using this measure in my Value field of my column chart:

 

 

Count for TopN Consignees = IF([Rank]<=[SelectedTopNNumber],'VMS Vehicle'[Count],"")

Where Rank:

 

 

Rank = RANKX(ALL('VMS Vehicle'[Consignee]),'VMS Vehicle'[Count], ,DESC)

 

 

 

Capture.PNG

 

Help:

Is it possible to tweak the formula for Count for TopN Consignees above so that for all the other consignees not in my Top 10, they get shown as "Other" in the x-axis with their respective vehicle count summed together?

 

Thanks in advance for your help

 

Wes

1 ACCEPTED SOLUTION

Hi @wes-shen-poal,

 

After reviewing your shared pbix file, I find that you may need to modify the following three measures, then it should work as expected. And I have sent you the modified pbix file in private message. Smiley Happy

 

Count = 
VAR currentConsignee =
    FIRSTNONBLANK ( Consignee_List[Consignee], 1 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'VMS Vehicle'[VehicleID] ),
        FILTER ( 'VMS Vehicle', 'VMS Vehicle'[Consignee] = currentConsignee )
    )
Rank = RANKX(ALL('Consignee_List'[Consignee]),[Count], ,DESC)
Count for TopN Consignees = 
VAR othersCount =
    SUMX (
        FILTER ( ALL ( Consignee_List ), [Rank] > [SelectedTopNNumber] ),
        [Count]
    )
RETURN
    IF (
        HASONEVALUE ( Consignee_List[Consignee] ),
        SWITCH (
            VALUES ( Consignee_List[Consignee] ),
            "Other", othersCount,
            IF ( [Rank] <= [SelectedTopNNumber], [Count] )
        )
    )

r5.PNG

 

Regards

View solution in original post

8 REPLIES 8
v-ljerr-msft
Employee
Employee

Hi @wes-shen-poal,

 

According to your description above, you should be able use a similar solution mentioned in this article to get your expected result. Smiley Happy

 

1. Create a new table with a list of all Consignee and an extra member for Others.

Consignee_List = UNION ( VALUES ( 'VMS Vehicle'[Consignee] ), ROW ( "Consignee", "Others" ) )

2. Then you should be able to use the formula below to create a new measure and show it on your chart to get TopN Consignees + Other.

measure =
VAR othersCount =
    CALCULATE (
        SUM ( 'VMS Vehicle'[Count] ),
        FILTER ( ALL ( 'VMS Vehicle' ), [Rank] > [SelectedTopNNumber] )
    )
RETURN
    IF (
        HASONEVALUE ( Consignee_List[Consignee] ),
        SWITCH (
            VALUES ( Consignee_List[Consignee] ),
            "Other", othersCount,
            IF ( [Rank] <= [SelectedTopNNumber], 'VMS Vehicle'[Count] )
        )
    )

 

Regards

Hi @v-ljerr-msft

 

Thanks so much. I can't do CALCULATE( SUM('VMS Vehicle'[Count]) i think because it's a calculated measure and not a column.

So I just tried doing CALCULATE([Count] instead, but then it gives me blank visualisation.

 

I'm not sure if this is relevant, but each row in the table 'VMS Vehicle' we are using represents a car vehicle with a unique VehicleID. And each unique VehicleID has a Consignee. So therefore, one Consignee can have many VehicleIDs.

 

My Rank formula

Rank = RANKX(ALL('VMS Vehicle'[Consignee]),'VMS Vehicle'[Count], ,DESC)

...ranks the Consignees based on their count of VehicleIDs (or rows).

 

I am just wondering whether this piece of information is helpful to tweak the formulas you gave me so that it doesn't return blank visualisation?

 

 

Thanks
Wes

Hi @wes-shen-poal,

 

Could you share a sample pbix file which can reproduce the issue, so that I can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here, or sent it to me in private message. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Hi @v-ljerr-msft

 

I have privately messaged you the OneDrive link.

 

Thanks again for your help


Wes

Hi @wes-shen-poal,

 

After reviewing your shared pbix file, I find that you may need to modify the following three measures, then it should work as expected. And I have sent you the modified pbix file in private message. Smiley Happy

 

Count = 
VAR currentConsignee =
    FIRSTNONBLANK ( Consignee_List[Consignee], 1 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'VMS Vehicle'[VehicleID] ),
        FILTER ( 'VMS Vehicle', 'VMS Vehicle'[Consignee] = currentConsignee )
    )
Rank = RANKX(ALL('Consignee_List'[Consignee]),[Count], ,DESC)
Count for TopN Consignees = 
VAR othersCount =
    SUMX (
        FILTER ( ALL ( Consignee_List ), [Rank] > [SelectedTopNNumber] ),
        [Count]
    )
RETURN
    IF (
        HASONEVALUE ( Consignee_List[Consignee] ),
        SWITCH (
            VALUES ( Consignee_List[Consignee] ),
            "Other", othersCount,
            IF ( [Rank] <= [SelectedTopNNumber], [Count] )
        )
    )

r5.PNG

 

Regards

Thank you so much @v-ljerr-msft

 

It works great, however, as the x-axis now uses the Consignee from the Consignee_List table, I can't seem to use this column chart to cross-filter other charts.

 

 

Is it because there's no relationship established between Consignee_List table and VMS Vehicle table? If so, I tried establishing a relationship using "Consignee" in the Consignee_List table and the one in the VMS Vehicle table, but then I get an error saying that one of the columns must have unique values.

 

Is there a way I can get the cross-filtering between the charts working again?

 

As an extension to this, I was wondering if you can kindly show me the formula to get the % of Total Count for each Top Consignee +Other?

 

I will have it showing in the tooltip 

 

Thanks

Wes

Hi @v-ljerr-msft

 

Was wondering you are able to assist further with my queries in my previous post. If you'd prefer to start a new thread I can but I just thought it makes sense as my queries are based off the solution you provided. 🙂

 

Thanks in advance
Wes

Hi @wes-shen-poal,

 

Honestly, I had tried and spent a lot time to find a solution previously. However, it turns out that I was not able to. Smiley Mad So, sorry for the delay response. Smiley Happy

 

In this scenario, I would suggest you post it in a new thread, as others who are more experienced may have a solution. Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.