Forum Discussion

abbyyyyyy's avatar
abbyyyyyy
Regular Visitor
7 years ago

Power Bi - If condition - Returns multiple rows

Due to some privacy issues, I cannot share the exact file I am working on. But the problem is here, I am trying to assign a rank to the customer (id) based on their revenue. The revenue is calculated based on some other information. However, the problem is 

 

So, the function I put for Tranche is 

if(Revenue<=0,1,if(Revenue<=100,2,if(Revenue<=300,3,if(Revenue<=400,4,if(Revenue<=500,5,if(Revenue<=600,6,7))))))


And the screenshot is what I got, I don't understand why I have 7 rows for each id.

If I dont put the function, Power BI has 2 rows. So my question is why when I put in the function, it has 7 rows.

idRevenue
a100
b10000

Thanks

Need Help

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    Usually these results are due to a relationship issue or (more likely) adding Tranche to the visualisation changes the context and it 'blows up' as some people call it.

    Revenue is a measure?  We would have to see it please and a data model with sample data would be great (fake data is fine as long as it illustrates the issue)

    Also is Tranche a column or measure?

    • abbyyyyyy's avatar
      abbyyyyyy
      Regular Visitor
      7 years ago

      Thanks for the reply. This is what I am tring to do. Here I have three customers a,b and c. Column B is the flag of whether it is headquarter or not and C and D are month and daily revenue

      First thing I do is I pushed all the revenue to the "Yes" in column B. So for instance, row 2 and row 3 have the same id and the same month, so the revnenue is pushed up to the "yes" level. Yes has a value of 5 and No has 0. For those that flag is unknown, nothing needs to be  pushed up. 

      Avg Revenue adjust =
      if(
          Flag(HQ) = "No",
          0,
          if(
              Flag(HQ) = "Unknown",
              Main[Avg Daily ACR],
              CALCULATE(sum(average revenue),ALLEXCEPT(Current table,Fiscal Month,Main[GPID]))) ).
      After that, I created a june revenue column, only showing the values based on June Revenue and assign the tranches.
      When I selected id and tranches as a bar chart, the result should be each id has 2 rows, for each id, they have 7 different rows,

      Instead, for id a, it has 7 rows

       

      Thanks