cancel
Showing results for
Search instead for
Did you mean:
mrslyfox Regular Visitor

## 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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User

## Re: TopN Others

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

6 REPLIES 6
v-haibl-msft Super Contributor

## Re: TopN Others

@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 Regular Visitor

## Re: TopN Others

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 Super Contributor

## Re: TopN Others

@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 Regular Visitor

## Re: TopN Others

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 Super Contributor

## Re: TopN Others

@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

Highlighted Super User

## Re: TopN Others

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!