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...
Need Help
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