Forum Discussion
Using filter and sum function inside an IF function
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.
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.
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.
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.
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.
Ashish_Mathur thanks for this solution. It worked for me.
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.
