TopN Others

[mrslyfox]

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 for each customer '1'

What is wrong ?

[v-haibl-msft]

@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]

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]

@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]

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 · Accepted solution]

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]

@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