Forum Discussion

mrslyfox's avatar
mrslyfox
Helper II
9 years ago
Solved

TopN Others

Hello Have two tables D_Customer and F_Invoice (one to many rationship) added to D_Customer calculated column RANK_M2 = RANKX(F_Invoice,SUMX(F_Invoice,F_Invoice[M2])) as result new column showing ...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi mrslyfox,

     

    Transform the RANK column to this measure:

     

    Rank = RANKX(ALLSELECTED(D_Customer[Customer]),CALCULATE(sum(F_Invoice[M2])))

     

    Then add a measure for others with the following formula

     

    Others Total = IF( ISBLANK(CALCULATE(sum(F_Invoice[M2]),FILTER(all(D_Customer[Customer]),[Rank]> 20))),0,CALCULATE(sum(F_Invoice[M2]),FILTER(all(D_Customer[Customer]),[Rank]> 20)))

     

    Add you TodayMoth = 1 in your visual filter and this should work.

     

    Regards,

    MFelix

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

mrslyfox

 

Please try this formula which should work in your scenario.

 

Rank_M2 =
RANKX ( D_Customer, SUMX ( RELATEDTABLE ( F_Invoice ), F_Invoice[M2] ) )

 

 

Best Regards,
Herbert

  • mrslyfox's avatar
    mrslyfox
    Helper II
    9 years ago

    Hello v-haibl,

    The formula is not working:

     

     

    Another related issue I have

    Addedd a new column to D_Customer table RANK_M2 = RANKX(D_Customer,CALCULATE(SUMX(F_Invoice,F_Invoice[M2]),D_Date[IsTodayMonth]=1))

    Addedd a new Visual Card at the Report with Sales M2 and Visual Filter where  D_Date[IsTodayMonth]=1

    From the first point of view everething is calculated in a right way, but once I perform RLS, Visual card showing wrong number.

     

     

    Maybe it could be helpfull to know model realationships I have:

     

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      mrslyfox

       

      The formula didn't work because you didn't use the related function in it. Please check the formula I provided before.

       

      About another related issue. I have twoquestions.

      1. Which visual do you use to show OTHERS and GRAND TOTAL?
      2. What is the field or formula for OTHERS and GRAND TOTAL?

       

      Best Regards,
      Herbert

       

      • mrslyfox's avatar
        mrslyfox
        Helper II
        9 years ago

        Hello v-haibl

         

        Here is result with suggested by you formula

         

        to show OTHERS and GRAND TOTAL I use 'Card'

        Grand Total is just Sum of F_Invoice[M2]

        For 'Other' I use also next filtering:

         

        IsTodayMonth = IF((Month(Today()-1)&"-"&Year(Today()-1))=(D_Date[DATE_MonthNumber]&"-"&D_Date[DATE_YearNumber]),1,0)