Forum Discussion
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 for each customer '1'
What is wrong ?
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
6 Replies
Please try this formula which should work in your scenario.
Rank_M2 = RANKX ( D_Customer, SUMX ( RELATEDTABLE ( F_Invoice ), F_Invoice[M2] ) )
Best Regards,
HerbertHello 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:
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.
- Which visual do you use to show OTHERS and GRAND TOTAL?
- What is the field or formula for OTHERS and GRAND TOTAL?
Best Regards,
Herbert
