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