Forum Discussion

Do5779's avatar
Do5779
Helper II
3 years ago

subquery SQL in Power BI - advanced editor

Hi, I have the following problem with SQL in Power BI advanced editor. 
This ist my SQL code and I want to select every product with the highest average price. 

The productnumber is the product-ID for the identification, "Quantity" is the quantity of the product and "QuantityPrice" the total amount of the buy. 

Everything works fine apart from the subquery. Is I want to execute this part I get the following message:

 

Microsoft SQL: Divide by zero error encountered. 

 

But there is no Divide by zero in the wohle calculation. Does anyone have an idea😐?

 

   With Test1 as
    (Select 
T1.productnumber as productnumber ,
        T1.Quantity,
        T1.QuantityPrice as QuantityPrice
   T1.Quantity/ T1.QuantityPrice as Price_per_product

from Table1 T1)

Select *, max(Price_per_product) over Test1.productnumber 

where Price_per_product = (select ax(Price_per_product) over Test1.productnumber from Test1)

 

 

 

 

2 Replies