Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrslyfox
Helper II
Helper II

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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
v-haibl-msft
Employee
Employee

@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

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

@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

 

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)

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.