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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Using filter and sum function inside an IF function

Hi,

I have imported the following dataset into the Power BI desktop.

The column Cost for each service for each company is currently calculated as count x cost per unit but it should be calculated as below,

For Device Services

Cost = Count of Device Services x cost per unit of Device Services

For Identity Services

Cost = (Count of Identity Services-Count of Device Services) x cost per unit of identity services.

So far I tried the below DAX expression to create a new column and it's not working. (Name of the table is SC Counts)

Cost_rev = IF('SC Counts'[Category]="Silver",'SC Counts'[Cost],(CALCULATE(SUM('SC Counts'[Count]),KEEPFILTERS('SC Counts'[Category] = "Gold"))-CALCULATE(SUM('SC Counts'[Count]), KEEPFILTERS('SC Counts'[Category]="Silver")))*1750)

Can anyone help me with this?

Thanks.

2 ACCEPTED SOLUTIONS
Super User

Hi,

This calculated column formula works

``Column = if(Data[Service]="Device services",Data[Count]*Data[Cost Per Unit],(Data[Count]-CALCULATE(SUM(Data[Count]),FILTER(Data,Data[Company]=EARLIER(Data[Company])&&Data[Service]="Device services")))*Data[Cost Per Unit])``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Here is my proposed measure

``````Cost = if(SELECTEDVALUE('Table'[Service])="Device Services",sum('Table'[Count])*sum('Table'[Cost Per Unit]),
var c= sum('Table'[Count])-CALCULATE(sum('Table'[Count]),allexcept('Table','Table'[Company]),'Table'[Service]="Device Services")
return c*sum('Table'[Cost Per Unit])
)``````

PBIX is attached.

7 REPLIES 7
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Frequent Visitor

Hi @lbendlin,

I have copied and pasted the source data into a table.

 Company Service Category Count Cost Per Unit ABC Device Services Silver 322 750 EFG Device Services Silver 139 750 XYZ Device Services Silver 31 750 MNO Device Services Silver 41 750 PQR Device Services Silver 600 750 ABC Identity Services Gold 434 1750 EFG Identity Services Gold 334 1750 XYZ Identity Services Gold 53 1750 MNO Identity Services Gold 61 1750 PQR Identity Services Gold 612 1750

Also here is a screenshot of the expected outcome.

Appreciate it if you could help me with this.

Super User

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Here is my proposed measure

``````Cost = if(SELECTEDVALUE('Table'[Service])="Device Services",sum('Table'[Count])*sum('Table'[Cost Per Unit]),
var c= sum('Table'[Count])-CALCULATE(sum('Table'[Count]),allexcept('Table','Table'[Company]),'Table'[Service]="Device Services")
return c*sum('Table'[Cost Per Unit])
)``````

PBIX is attached.

Frequent Visitor

@lbendlin this measure worked for me. Thank you!

Super User

Hi,

This calculated column formula works

``Column = if(Data[Service]="Device services",Data[Count]*Data[Cost Per Unit],(Data[Count]-CALCULATE(SUM(Data[Count]),FILTER(Data,Data[Company]=EARLIER(Data[Company])&&Data[Service]="Device services")))*Data[Cost Per Unit])``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

@Ashish_Mathur thanks for this solution. It worked for me.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors