Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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.
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.
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.
You are welcome.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |