Forum Discussion

v-mig's avatar
v-mig
Frequent Visitor
9 years ago
Solved

Need help with IF(SUMIF... in PowerBi

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 eac...
Need Help
  • MattAllington's avatar
    MattAllington
    9 years ago

    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's avatar
v-mig
Frequent Visitor
9 years ago

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 "