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.
So I have Budgeted cost which is coming from Planned dates i.e. Finish date and then Earned(Acutal) Cost coming from the Actual Finish dates.
Earned Cost and Budgeted cost are different values with their own dates but needs to be shown on same axis. Earned is coming from Actual dates and Budgeted cost is coming from Finish (Planned Dates). How to show them on same axis or in a same chart.
Both of them are in the same table.
I created a Monthly bins using Planned dates and plotted the Budgeted cost on it. But when I put the Earned Cost on the same graph it is showing Earned Cost same as Budgeted cost (4.42Mil and 3.43Mil) which is not correct Earned cost on that month. The earned cost should show its value based on its own Acutal dates, that is where it comes from.
Budget and Earned this is actually the asnwer , Earned in month of August is actually 2.2M Aug and 5.17M in Sept.
This is the Data in the table
So the Item Month Bin is created from Finish Dates (Planned Finish Dates), Acutal Finish bin is created from Acutal Finish Dates
If this is possible to do without Bin that would be okay, I used bins to have the Summarized data as per the "MMYY" in format of Aug2019, Sept2019 and so on)
Solved! Go to Solution.
Hi @Anonymous ,
I suppose that you to create a new table to display distinct date firstly and then you need to create two measures to compute the sum in different fields.
You can see the formulas and the sample result below.
New Table: Calender = DISTINCT(UNION(DISTINCT(Cost[ActualFinish(bins)]),DISTINCT(Cost[Month Bin]))) Measures: BudgetMeasure = CALCULATE ( SUM ( Cost[BudgetedCost] ), FILTER ( ALLSELECTED ( Cost ), 'Cost'[Month Bin] IN FILTERS ( 'Calender'[dates] ) ) ) Actual = CALCULATE ( SUM ( Cost[EarnedValueCost] ), FILTER ( ALLSELECTED ( Cost ), 'Cost'[ActualFinish(bins)] IN FILTERS ( 'Calender'[dates] ) ) )
Best Regards,
Jack Chen
Hi @Anonymous ,
I suppose that you to create a new table to display distinct date firstly and then you need to create two measures to compute the sum in different fields.
You can see the formulas and the sample result below.
New Table: Calender = DISTINCT(UNION(DISTINCT(Cost[ActualFinish(bins)]),DISTINCT(Cost[Month Bin]))) Measures: BudgetMeasure = CALCULATE ( SUM ( Cost[BudgetedCost] ), FILTER ( ALLSELECTED ( Cost ), 'Cost'[Month Bin] IN FILTERS ( 'Calender'[dates] ) ) ) Actual = CALCULATE ( SUM ( Cost[EarnedValueCost] ), FILTER ( ALLSELECTED ( Cost ), 'Cost'[ActualFinish(bins)] IN FILTERS ( 'Calender'[dates] ) ) )
Best Regards,
Jack Chen
Hi Jack Chen @v-shex-msft ,
Thank you so much for the reply.
Below is the correct values, I used your formula and it works really great for a bar chart,
But when the data is plotted in a Table it is showing all the valus as same for all, I think it is because of the relationship between the calendar and data table.
How to make relationship between the "Task" Table and "Calendar" Table.
Do i need to make a copy of the "Task" table or make copy of "Calendar " Table ?
I did try to make relationship between the Task and calendar, but I am getting the following error;
I also tried to different realtionhip type from ManytoMany , OnetoMany, etc. but the error remains the same.
I have uploaded the pbix file here if you want to see, https://we.tl/t-mCAed3cTWj
Many Thanks
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |