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

 

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)

 

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