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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
chrisgehm
Helper III
Helper III

Divide and TOP 10

Hi everyone!

 

I have 1 table in excel with 3 columns

 

Amount1 - Amount2 - Provider

 

I've create a new column in Power BI that makes the difference between Amount1 and Amount2

Lets call it Difference.

 

So now I have 4 columns:

Amount1 - Amount2 - Provider - Difference

 

So what I in one hand divide Difference/Amount1

And in the other hand the TOP 10 of providers with the greatest Amount1

 

When I try the division, I've tried 2 ways, and none of them worked fine.

Way 1: New Column = Table[Difference]/Table[Amount1]

Way 2: New Column = Divide(Table[Difference],Table[Amount1],0)

 

What I am doing wrong in the division, and how do I get the top  10?

 

Kind regards.

10 REPLIES 10
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @chrisgehm,

 

To your first question: divide Difference/Amount1, you said none of the two ways worked fine, can you describe more clearly? Any error prompt? Don't they give you the correct result? Or don't they give any value? Based on my test, both of these formulas worked well.
1.PNG

 

To your second question: get the TOP 10 of providers with the greatest Amount1. 

 

Please create a new table, refering to this DAX formula:

TopN = TOPN(10,Top10,TableName[Amount1],DESC)

 

Alternatively, you can also add visual level filters (not page level filter and report level filter) to display only Top 10 records. Pay attention, TopN option is only available under Visual level filters tab.
2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yulgu-msft when I make the division with divide(), the result is incorrect

@v-yulgu-msft

When i make the division with "/" the result shows me: NaN or -Infinity

Hi @chrisgehm,

 

Please click the arrow down under 'Filter Type' not 'Show items when the value:' to select 'Top N' option.

1.PNG

 

Please check whether there existing blank or 0 values in column [Difference] and [Amount1], blank value or 0 value will lead to Nan or Infinity when using divide.

 

If that is the case, please try this formula:

New Column = IF(ISBLANK('Table'[Amount1]) || ISBLANK('Table'[Difference]) || 'Table'[Amount1])=0 || 'Table'[Difference]=0,BLANK(), DIVIDE('Table'[Difference],'Table'[Amount1])))

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi!

When i divide, it doesn't show the correct results, and some times it changes me the original number in the report.

 

And in the filter, I can't se that option (TOPN)

 

Sin título.jpg

Phil_Seamark
Microsoft Employee
Microsoft Employee

What do you need the top 10 for?  Is this to limit how many you display on a grid or in a visual?

 

If so you can do this using report fiters.  If this works for you your DAX calc can be much simplier and if you provide a small sample of your data we can help with that.

 

top n.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sorry, can't find where is that part you show in the image

 

Sorry, here is a slightly taller picture.  When you drag an item to the Axis of a visual, down in the Filters you have additional options to filter that Axis.  Here you can select the Top (or Bottom) N based on other values.

 

top n 2.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Sorry, still can't find the filter.

 

Captura.PNG

See right at the very bottom of your image, the Filter Type drop dow says "Advanced Fitlering".  What happens when you click that?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.