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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.