Using filter and sum function inside an IF function

[deew95]

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 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.

[Ashish_Mathur · Accepted solution]

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]

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

[deew95]

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.

[lbendlin · Accepted solution]

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]

@lbendlin this measure worked for me. Thank you!

[Ashish_Mathur · Accepted solution]

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]

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

[Ashish_Mathur]

You are welcome.