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.
I need a help for following situation. I have to deal will two tables. 1. one has revenue data till date 2. second has forecast for sales from the current date. Hence I get the cumulative revenue from the revenue table till date. I use following measure and get following table.
mes = if(contains(RevenueData,RevenueData[Sale/Purchase],"Sales"),calculate(sum(RevenueData[Revenue]), filter(all(Calendar_Period),Calendar_Period[Date]<= max(Calendar_Period[Date]))),sum(RevenueData[Revenue]))
The line plot for this table reacts to a filter.
Again I have another measure for forecast sales. The measure and the table for this looks like this.
test3 = CALCULATE(sum(ChargeTable[Probablised]), ChargeTable[Status] in {"Secured", "Pipeline"},CROSSFILTER(ChargeTable[Period],Calendar_Period[Date],Both),filter(all(Calendar_Period), Calendar_Period[Date]>= EOMONTH(TODAY(),-1) && Calendar_Period[Date] <= max(Calendar_Period[Date])))
The line plot for this table reacts to the same table.
I want to create a third measure which is sum of these two measure
test4= [mes]+ [test3]
Here I am finding the issue, I expect the values from August will add the foreast of the month and the revenue till date. e.g. 14,617,437.03 + 2,778,236.57. But I get cumulative values of individual tables.
I was able to get the intended table modifying the first measure as following
Revenue_Cumulative = CALCULATE(sum(RevenueData[Revenue in GBP_1]),filter(all(RevenueData), RevenueData[Sale/Purchase] in {"Sales"} && RevenueData[Month] <= max(Calendar_Period[Date]) ))
and get following table
But the same filter reacts only to the second measure test3 and this does not have any effect on the values of the updated "mes" measure.
How can I get the cumulative vales for the combined tables and the line graph for the combined table should react to the same filter.
Hi @avinrosh,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi @avinrosh,
Due to I could not reproduce your data structure, I have entered some sample data, you could refer to it:
1.Sample data(I have entered two measures):
Measure1 = CALCULATE(SUM(Table1[value]))
Table 1
Measure2 = CALCULATE(SUM(Table2[value]))
Table 2
2.Create a calendar table, a month column and create relationships.
New Table:
Table = CALENDAR("2018/1/1","2018/12/31")
New column:
Month name = FORMAT('Table'[Date],"MMMM")
3.Create a measure and a Table visual. Add the related field, you can see the correct result.
Measure 3 = var a=[Measure1]
var b=[Measure 2]
return a+b
If I misunderstood you, could you please offer me more information about your data structure or share your pbix file if possible?
You can also download the PBIX file to have a view.
Regards,
Daniel He
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |