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

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

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

        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. :smileymad: So, sorry for the delay response. :smileyhappy:

         

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

         

        Regards