Forum Discussion

wes-shen-poal's avatar
wes-shen-poal
Helper III
9 years ago
Solved

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 Consigne...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    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] )
        )
    )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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. :smileyhappy:

 

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

  • wes-shen-poal's avatar
    wes-shen-poal
    Helper III
    9 years ago

    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

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      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. :smileyhappy:

       

      Regards

      • wes-shen-poal's avatar
        wes-shen-poal
        Helper III
        9 years ago

        Hi v-ljerr-msft

         

        I have privately messaged you the OneDrive link.

         

        Thanks again for your help


        Wes