Forum Discussion

deew95's avatar
deew95
Frequent Visitor
4 years ago
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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

  • lbendlin's avatar
    lbendlin
    4 years ago

    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.

deew95's avatar
deew95
Frequent Visitor
4 years ago

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.

Appreciate it if you could help me with this.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

    • deew95's avatar
      deew95
      Frequent Visitor
      4 years ago

      Ashish_Mathur thanks for this solution. It worked for me.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        You are welcome.

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

    • deew95's avatar
      deew95
      Frequent Visitor
      4 years ago

      lbendlin this measure worked for me. Thank you!