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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
deew95
Frequent Visitor

Using filter and sum function inside an IF function

Hi,

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

deew95_0-1649405711333.png

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

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.

Untitled.png


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

View solution in original post

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.

View solution in original post

7 REPLIES 7
lbendlin
Super User
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

Hi @lbendlin,

 

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

CompanyServiceCategoryCountCost Per Unit
ABCDevice ServicesSilver322750
EFGDevice ServicesSilver139750
XYZDevice ServicesSilver31750
MNODevice ServicesSilver41750
PQRDevice ServicesSilver600750
ABCIdentity ServicesGold4341750
EFGIdentity ServicesGold3341750
XYZIdentity ServicesGold531750
MNOIdentity ServicesGold611750
PQRIdentity ServicesGold6121750

 

Also here is a screenshot of the expected outcome.

deew95_0-1649543268342.png

Appreciate it if you could help me with this.

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.

@lbendlin this measure worked for me. Thank you!

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.

Untitled.png


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

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

You are welcome.


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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