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

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

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

      mrslyfox

       

      Did you add Visual level filters "IsTodayMonthi is 1" for Grand total?

      If possible, could you please provide your PBIX file to me? You can use https://www.mockaroo.com/ to make some dummy data but with same tables and relationship, so that we can have a try locally.

       

      Best Regards,
      Herbert