Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
im new to PowerBI. I have a table.
Oder_ID | Project_ID | Project_Name | Earnings |
1001 | 5001 | Project A - Part1 | 100000 |
1002 | 5001 | Project A - Part2 | 15000 |
And a Messure calculating project-costs for a Project ID (Costs for 5001 are 60000).
I have Earnings-Costs=CALCULATE(SUM(Table1[Earnings])-Table2[Costs]) and i get
Project_ID | Earnings | Costs | Earnings-Costs |
5001 | 100000 | 60000 | 40000 |
5001 | 15000 | 60000 | -45000 |
But i want:
Project_ID | Earnings | Costs | Earnings-Costs |
5001 | 115000 | 60000 | 55000 |
Does anyone have a sollution?
Thanks in advance.
Solved! Go to Solution.
This was almost it and i figured it out.
The solution was:
Cost measure=sum(Table[Cost])
and a final messure
Final measure=[Earning measure]-[Costs]
Your line "Cost measure=sum(Table[Cost])" wasn't required. I could just use my Costs-Messure without "sum".
It seems to work in seperate Messures but not in an "CALCULATE()" like my original aproach
"Earnings-Costs=CALCULATE(SUM(Table1[Earnings])-Table2[Costs])"
Thank you. Your Reply helped me to aproach it differently. I'm still lerning.
Earning measure=sum(Table[Earning])
This will return sum of values for id.
Cost measure=sum(Table[Cost])
It will return 120000 not 60000 as there are two same cost for same id.
Final measure=[Earning measure]-[Cost measure]
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
This was almost it and i figured it out.
The solution was:
Cost measure=sum(Table[Cost])
and a final messure
Final measure=[Earning measure]-[Costs]
Your line "Cost measure=sum(Table[Cost])" wasn't required. I could just use my Costs-Messure without "sum".
It seems to work in seperate Messures but not in an "CALCULATE()" like my original aproach
"Earnings-Costs=CALCULATE(SUM(Table1[Earnings])-Table2[Costs])"
Thank you. Your Reply helped me to aproach it differently. I'm still lerning.
if the cost remains constant then simple take max of cost.
Cost measure=Calculate(MAX(Table[Cost]))
Add project add, Earning measure and cost measure, and final measure in table visual.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
CALCULATE(MAX(Table2[Costs]) doesnt work. Can't be used on a Messure.
Project_Costs is constant for each Project_ID. Table1 is only an example. There are severel projects. Mostly with one order but some with 2 or 3.
Please share your exact dataset.
Tables with column name so that it will be easy for me to answer your quetion.
Thanks for your relpy.
The Messure for Project_Costs is a sum of everything spend for a Project_ID. I have 2xCost (in this case) if it is summed up .
The orders_table comes from a database where there can be multiple orders for a Project_ID.
I know its not great but that is the data i have.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |