Forum Discussion

jayvataliya's avatar
jayvataliya
Frequent Visitor
5 years ago
Solved

Bar chart - Filter on this Visuals -> basic filtering where value = Measure

Hi All,

 

I have a bar chart representing Company and Amount.

  • A, B, C,... is the company name

 

I created a measure that gives me the top 1 fail reason by amount.

  1. Eg. Wrong Input 

 

I want to filter the chart where Reason  = Measure(Top 1 fail reason)

 

 

 

I tried many ways but no luck, your help will be appreciated.

 

Thanks

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi jayvataliya

    Keep the previous measure and create a visual control measure for each visual and apply it in the visual filter:

     

    visual control_1 = 
IF ( SELECTEDVALUE ( 'Source table'[FailReason] ) = [Top1 fail], 1 )

     

    Please refer the below sample file, hopes it could help.

     

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    "I created a measure that gives me the top 1 fail reason by amount."

     

    How?  Did you use RANKX ?

    • jayvataliya's avatar
      jayvataliya
      Frequent Visitor
      5 years ago

      Yes, you are right. I followed the below steps to get the top 1 fail reason.

       

      • I create a separate table using SUMMARIZE() from the main dataset (Columns: Year, Month, FailReason, Amount)
      • Created a measure to Sum the amount 
        • AmountSum = SUM('Table'[Amount])
      • Created a measure that gives the rank of fail reasons by the sum of the amount 
        • RK = RANKX(ALL('Table'[FailReason]),[AmountSum],, DESC, Dense)
      • And, created the last measure that gives me the top 1 fail reason by rank
        • TopFailReason = TOPN(1,VALUES('Table'[FailReason]),CALCULATE([RK] = 2),DESC)

       

      NOTE: here I am getting top 1 fail reason where Rank = 2(2nd fail reason)

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi jayvataliya ,

    Sorry for replying late. Based on your description, you actually create a measure to show the top1 fail reason name:

    Top1 fail =
VAR tab =
    SUMMARIZE (
        ALL ( 'Source table' ),
        'Source table'[FailReason],
        "Sum", SUM ( 'Source table'[Amount] )
    )
VAR tb =
    ADDCOLUMNS ( tab, "rank", RANKX ( tab, [Sum],, DESC, DENSE ) )
RETURN
    MAXX ( FILTER ( tb, [rank] = 1 ), [FailReason] )

    But you can not put the measure in the 'text' field in the filter when you are using Advanced filter.

    Instead, you can use the TopN filter directly to ger the top 1 fail reason by the sum of Amount for each reason.

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jayvataliya's avatar
      jayvataliya
      Frequent Visitor
      5 years ago

      Thank You v-yingjl  for your reply. It definitely helped to get the rank of fail reason.

       

      However, I am trying to display the data of each reason in a separate chart visual. 

      • Eg.
        • Chart 1 = Wrong Input
        • Chart 2 = AAA
        • Chart 3 = BBB
      •  

      At this point, the chart shows all of the reason values.

       

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi jayvataliya ,

        As I previous post, just adjust the TopN filter as Top1,2,3 for Amount for each bar chart so that you can get the expected output.

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.