Forum Discussion
Solved
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 calcul...
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.
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.
