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)

       

      • MFelix's avatar
        MFelix
        Super User
        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