cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrslyfox Regular Visitor
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
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
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] ) )

 

TopN Others_1.jpg

 

Best Regards,
Herbert

mrslyfox Regular Visitor
Regular Visitor

Re: TopN Others

Hello v-haibl,

The formula is not working:

RANK.png

 

 

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.

 

TopN-RLS_On_GlobalUser.pngTopN-RLS_On.png

 

Maybe it could be helpfull to know model realationships I have:

 

Model structure.png

v-haibl-msft Super Contributor
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
Regular Visitor

Re: TopN Others

Hello v-haibl

 

Here is result with suggested by you formula

 

Other_relatedtable.png

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:

others.png

 

IsTodayMonth = IF((Month(Today()-1)&"-"&Year(Today()-1))=(D_Date[DATE_MonthNumber]&"-"&D_Date[DATE_YearNumber]),1,0)

 

v-haibl-msft Super Contributor
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
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!