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

 

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