Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Top N Table Power Query

  • 1.  Top N Table Power Query

    Bronze Contributor
    Posted 30 days ago

    Hi,

    Is there a way to create a table in Power Query to get Top N organizations dynamically?

    I have some visuals and I want them all to present data from my Top N organizations, but Top N organization are different depending on the values in my slicers.

     

    I've solved it for one visual with the following DAX measures:

    SelectedTopNNumber = IF(HASONEVALUE('TopN'[Top]);Min('TopN'[Top]);10)
    AntalEval = COUNT('table1'[StatisticKey])
    OrgRankByTopN = IF(HASONEVALUE('dimOrg'[OrgName]);RANKX(ALLSELECTED('dimOrg'[OrgName]); [AntalEval]))
    ShouldOrgBeIncluded = IF([OrgRankByTopN]<=[SelectedTopNNumber];1;0)
    And the visual looks like this:
    Skärmklipp1.PNG
    But I don't know how to do it for the visual below. I want it to present AntalEval for my Top N organizations. But I don't know how to do it when OrgName is not in the visual.
    Skärmklipp2.PNG
     
     

    So I was wondering if there's someway to create a table which consist of Top N organizations and use that. But it must be made dynamic since I have slicers on date and so on which change the Top N organizations.



    ------------------------------
    Rebecka Nylin
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Top N Table Power Query

    Top Contributor
    Posted 30 days ago
    I don't think Power Query is the right place to do that. It is dynamic at the point of refresh - that is when the ranks would get set.
    Any filters would filter the ranked data, not re-rank the data based on the filter.

    The visuals have TopN filters built in though.

    For your second visual:
    Put OrgName into the visual filter panel. (Not the visual itself)
    Go to the OrgName filter, select Top N filter, enter N,  and add the AntalEval measure as the "By Value".
    Click on apply filter.

    In any case....a DAX calculated column or calculated table should also work.
    Let me know if the above does the trick.


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Top N Table Power Query

    Bronze Contributor
    Posted 29 days ago
    Hi,
    My N is dynamic and decided by a slicer, so I cannot use it in Top N filter unfortunately.
    Best Regards, Rebecka

    ------------------------------
    Rebecka Nylin
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Top N Table Power Query

    Gold Contributor
    Posted 29 days ago

    @Rebecka Nylin ​See if this works for you. It's not a top 10, but the logic should work. Create a table that has the top N values listed (you can do this in power query or DAX)

    In my example below DaysAsMember is my table that contains the list of values in my slicer.

    create a measure in your main table that returns the chosen top n:
    NewMemberDays = SELECTEDVALUE(DaysAsMember[DaysAsMemberPriorToHelium])

    create another measure that compares the top n value to the value from the main table 

    NewOrExisting_Member = IF(SELECTEDVALUE(Helium_Savings_Query[DaysAsMemberPriorToHelium])<=[NewMemberDays],"New Member","Existing Member")

    then on the visuals (maybe you can put it on page) set the filters to be equal to the value of the if statement above


    ------------------------------
    Sam Duval
    Data Quality Analyst
    Indianapolis
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Top N Table Power Query

    Bronze Contributor
    Posted 29 days ago
    Hi,
    No, sorry this doesn't work.
    Maybe it can help me with the Top N filter for the main visual (but I already have a nice solution for that), but not for the other visual.
    And you cannot put a measure as a page filter.
    Regards, Rebecka

    ------------------------------
    Rebecka Nylin
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Top N Table Power Query

    Gold Contributor
    Posted 29 days ago

    I missed a step in my previous reply the value in the top left corner of the visual is calculated via sumx and filter combination. You might be able to change/create a second measure in table that is a countx and layer in your ShouldOrgBeIncluded into the equation.

    NewMemberDays = SELECTEDVALUE(DaysAsMember[DaysAsMemberPriorToHelium])

    New_Member_%_New_Money = SUMX(FILTER(Helium_Savings_Query,Helium_Savings_Query[DaysAsMemberPriorToHelium]<=[NewMemberDays]),Helium_Savings_Query[NewMoney])




    ------------------------------
    Sam Duval
    Data Quality Analyst
    Indianapolis
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Top N Table Power Query

    Top Contributor
    Posted 29 days ago
    Oh. I misunderstood the requirement.
    I thought you were referring to the fact that the Top N Organizations would change depending on the slicers.
    If the N itself is dynamic (could be Top 1, Top 5, Top 10) then that is more complicated.

    Let me give it some additional thought.

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200