Forum Discussion

Lefuneste57's avatar
Lefuneste57
Icon for Helper I rankHelper I
2 years ago
Solved

How to create a segment with N first line from a table report order bye amount value ?

Hi, i have make a report with this data for exemple,i have many segment filter in my page. So i would know how can i make a segment filter to choose the number of  line (order by amount) to disp...
  • mark_endicott's avatar
    mark_endicott
    2 years ago

    Lefuneste57 - Just to clarify, my solution will work for you & it is dynamic (because it uses a measure), so the ranking will be re-calculated according to any filters you use on the page. 

     

    But you will need to use the measure below in any visual that you want to see this behaviour. There is no need to use a page filter because the measure handles the visual filtering. So if you want to filter an entire page, place the measure in every visual. 

     

    It works by only displaying the amount value when the rank number is lower than or equal to the number in the slicer. The slicer is created using these instructions: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

     

     

    VAR Number_to_Rank = [Number of Lines (by order amount) Value] // from the numeric range parameter
VAR _amount = [_amount] // sum( amount )
RETURN
    IF (
        RANKX ( ALLSELECTED ( 'Table (6)'[Item], 'Table (6)'[Build] ), [_amount] ) <= Number_to_Rank,
        _amount
    ) // Returns the amount for any items that with a rank within the numbers specified in the Numeric range parameter.

     

     

     

     

mark_endicott's avatar
mark_endicott
Icon for Super User rankSuper User
2 years ago

Lefuneste57 - You can do this with RANKX, and you will also need to create numeric range parameter. 

 

Once you have created one of them, you can use the measure it creates as part of another measure that dynamically shows the amount, for the number of items you select. Below is my final measure, I will explain underneath:

 

VAR Number_to_Rank = [Number of Lines (by order amount) Value]
VAR _amount = [_amount]
RETURN
    IF (
        RANKX ( ALLSELECTED ( 'Table (6)'[Item], 'Table (6)'[Build] ), [_amount] ) <= Number_to_Rank,
        _amount
    )

 

The measure in Number_to_rank is from the numeric range parameter and presents whatever number is selected in the parameter. 

The _amount measure is a sum of Amount

The RANKX gives the ranking by _amount for the two colomns in your table, which are inside the ALLSELECTED. 

and the IF wrapper shows the amount for any rank that is less than or equal to the number from the Number_to_rank variable. 

 

Please mark this as the solution if it works for you!

  • Lefuneste57's avatar
    Lefuneste57
    Icon for Helper I rankHelper I
    2 years ago

    Hi,

    Thanks for your answer but

    I don't have the [Number of Lines (by order)] column in my report table. I added it in my example to show that i needed to sort by amount but I have to create this column and I don't know how.
     
    Thanks
     
    • mark_endicott's avatar
      mark_endicott
      Icon for Super User rankSuper User
      2 years ago

      Lefuneste57 - Sorry, I thought you may already be able to create a Numeric Range Parameter for the first part of my solution. 

       

      Here are the instructions, https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if, once you have followed these, you will get a measure in the new parameter table that you can reference in your Dynamic Rank measure.

       

      In my solutuion, the measure that is auto generated by the parameter is 

      [Number of Lines (by order amount) Value]

      because I have named the parameter "Number of Lines (by order amount)"