Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
v-mig
Frequent Visitor

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 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

1 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

 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

9 REPLIES 9
v-sihou-msft
Employee
Employee

@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,

 

 

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.

 

Capture.PNG

 

Below is the snapshot from PowerBI

Capture.PNG

v-mig
Frequent Visitor

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

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/ 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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
Frequent Visitor

@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.

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

 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
v-mig
Frequent Visitor

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
Frequent Visitor

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 "

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.