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)

 

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