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?
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.
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.
Please click the arrow down under 'Filter Type' not 'Show items when the value:' to select 'Top N' option.
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])))
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)
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.
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.
See right at the very bottom of your image, the Filter Type drop dow says "Advanced Fitlering". What happens when you click that?