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

 

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