Need help with IF(SUMIF... in PowerBi

[v-mig]

Hi

I'm trying to create a formula which will calculate the sum of revenue (pipe table) of all the opportunities in the pipe table and then the condition. For ex if Oppty A has 3 opportunities each with different revenue value then the out put should SUM all 3 values of all 3 opportunities and return if >30K or < 30K

In Excel-lingo this would be something like:

I`m trying to convert this " IF(SUMIF(B:B,[@[Column ID]],N:N)>30000,">30K","<30K") " Excel formula into powrbi. I`m unable to proceed any further than this " 30K Qualifier = CALCULATE(SUM(Pipeline[Qualified Pipe]), FILTER(Pipeline,Pipeline[Opportunity ID]),sum(Pipeline[Qualified Pipe]) "

I also referred to other tags in the community but couldnt find anything relevant to my ask, can anyone help please?

Thanks

[v-sihou-msft]

@v-mig

Based on your description, you want to apply the condition on total revenue group on [Opportunity ID]. Please try formula below:

=
IF (
    CALCULATE (
        SUM ( Pipeline[Qualified Pipe] ),
        ALLEXCEPT ( Pipeline, Pipeline[Opportunity ID] )
    )
        > 30000,
    ">30K",
    "<=30K"
)

Regards,

[v-mig]

Thanks @v-sihou-msft

But the formula doesnt seem to work 😞 it turns everything to >30K and also the slicer doesnt apply to this condition. Below snapshot is an example of what I`m looking at. If that helps.

Below is the snapshot from PowerBI

[v-mig]

@MattAllington Would you be able to help me with this please.

[MattAllington]

well of course this is doable, but the problem I see is that you are refering to details that I don't see in the data.  the answer depends on your table names, column names, relationships etc.  You refer about Account A, but I don't see any refernce to Account in the data you have provided.  I suggest you post a proper sample workbook as I recommend here  http://exceleratorbi.com.au/get-help-power-bi/ 

[v-mig]

Thanks @MattAllington By account A what i mean here is Opportunity ID. So if there are identical opportunity ID`S Ex. 7-INMYXMyhf as shown in the snap shot. I should be able to sum the qualified pipe for all those ID`s and result as < or > 30K.

[v-mig]

@Greg_Deckler, @MarcelBeug, @vanessafvg I see that you`ll being a top contributer, hoping if one of you could help on the same.

Thanks in advance.

[MattAllington · Accepted solution]

So in the absence of all the information, I will assume that you have loaded this data into a table called myTable.  If this was me,  I would add a new table using the new table button with the following formula.

Accounts = ADDCOLUMNS(SUMMARIZE(myTable,myTable[Opportunity ID]),"30k Qual",if(CALCULATE(sum(myTable[Qualified Pipe]))>=30000,">30k","Less Than"))

Then join the new table "Accounts" to myTable using the Opportunity ID as the key.  You can then use the new column in the Accounts table as part of the data model

[v-mig]

So basically If Account A has 3 opportunities AA - $100, AB - $50 and AC - $25 and Account B has 2 Opportunities BA - $40 and BB - $60 I need to SUM all the opportunities under account A which would be $175 and Account B which would be $100 and IF the total sum of opportunities under each account is less than $100 then "<$100" else ">$100".

[v-mig]

I also tried "

30K Qualifier = IF(CALCULATE(SUM(Pipeline[Qualified Pipe]),ALL(Pipeline),Pipeline[Opportunity ID] = EARLIER(Pipeline[Opportunity ID]))>30000,">30000","<30000") " return an error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist "