Forum Discussion

chrisgehm's avatar
chrisgehm
Helper III
9 years ago

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 D...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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.

 

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.

 

Best regards,
Yuliana Gu

  • chrisgehm's avatar
    chrisgehm
    Helper III
    9 years ago

    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)

     

  • chrisgehm's avatar
    chrisgehm
    Helper III
    9 years ago

    v-yulgu-msft

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

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      Hi chrisgehm,

       

      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])))

      Regards,
      Yuliana Gu

  • chrisgehm's avatar
    chrisgehm
    Helper III
    9 years ago

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