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,

 

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    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

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

      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

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

        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