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 have been trying to get TotalYTD work. See the data and formula below :
1) For YTD Act, YTD_Fcst and YTD Var, TotalYTD is working as expected.
2) For YTD Var Abs, TotalYTD is not working as expected. I can not figure out why only this one is not working properly.
Thanks
??? | ||||||||
=totalYTD('OP - Data'[Actual], 'Date Key'[Date_key]) | =totalYTD('OP - Data'[Fcst], 'Date Key'[Date_key]) | =totalYTD( 'OP - Data'[Var], 'Date Key'[Date_key]) | =totalYTD ('OP - Data'[Var Abs], 'Date key'[Date_key]) | |||||
Actual = CALCULATE(sum('OP - Data'[Fcst_Act]), filter('OP - Data', 'OP - Data'[Fcst_N]=0 && 'OP - Data'[Fcst Type]="Actual")) | Fcst = CALCULATE(sum('OP - Data'[Fcst_Act]), filter('OP - Data','OP - Data'[Fcst_N]=4 && 'OP - Data'[Fcst Type]="Fcst" )) | New Measure | New Measure | working | working | working | not working | Need to be |
Actual | Fcst | Var (= Actual - Fcst) | Var Abs (= abs(Actual - Fcst) ) | YTD Act | YTD_Fcst | YTD Var | YTD Var Abs | YTD Var Abs |
726,247 | 467,896 | 258,351 | 258,351 | 726,247 | 467,896 | 258,351 | 258,351 | 258,351 |
653,244 | 572,124 | 81,120 | 81,120 | 1,379,491 | 1,040,020 | 339,471 | 339,471 | 339,471 |
513,018 | 606,354 | -93,336 | 93,336 | 1,892,509 | 1,646,374 | 246,135 | 246,135 | 432,807 |
367,854 | 629,864 | -262,010 | 262,010 | 2,260,363 | 2,276,238 | -15,875 | 15,875 | 694,817 |
Solved! Go to Solution.
Hi,
In your Matrix visual, i dragged Month from the Date Table to the Rows section. I wrote the folloing measure
YTD Var Abs = if(ISBLANK([Var Abs]),BLANK(),SUMX(CALCULATETABLE(SUMMARIZE(ALL('Date'[Month]),'Date'[Month],"ABCD",[Var Abs]),DATESBETWEEN('Date'[Date_key],DATE(YEAR(MAX('Date'[Date_key])),1,1),MAX('Date'[Date_key]))),[ABCD]))
Hope this helps.
Hi,
Aren't your last two numbers in the last column incorrect. The second last row's answer should remain 246,135 and the last row's answer should be 15,875. How can the answer be 694,817?
They are correrct.
694,817 = 258,351+81,120+93,336+262,010 |
Please refer to the fourth column. Thanks.
Hi,
Your picture is not clear. Is your last row a Grand Total row. Share the link from where i can download your PBI workbook and show the table where you want the result to appear.
See the example I created:
https://1drv.ms/u/s!AowzPecIR5bZhiJTAAlG0svwbiA5
The column "YTD Var Abs" is not calculating right.
Any thoughts? Thanks.
Hi,
I believe this is the answer you are expecting. This is my measure
=YTD Var Abs = if(HASONEVALUE('Date'[Date_key].[Month]),if(max('Date'[Date_key])<now(), totalYTD('Data'[Var Abs],'Date'[Date_key]),blank()),SUMX(SUMMARIZE(VALUES('Date'[Date_key].[Month]),[Date_key].[Month],"ABCD",if(max('Date'[Date_key])<now(), totalYTD('Data'[Var Abs],'Date'[Date_key]),blank())),[ABCD]))
Thank you for your reply. It does not get what I expect. I want "YTD Var Abs" to be calcuated based on the column - "Var Abs".
2018 | not working | ||||||
Month | Actual | Fcst | Var | Var Abs | YTD Var | YTD Var Abs ( incorrect) | YTD Var Abs I expect to be |
1 | 5 | 6 | -1 | 1 | -1 | 1 | 1=1 |
2 | 6 | 5 | 1 | 1 | 0 | 0 | 2 =(1+1) |
3 | 4 | 8 | -4 | 4 | -4 | 4 | 6 =(1+1+4 ) |
4 | 10 | 10 | 0 | 0 | -4 | 4 | 6 =(1+1+4+0) |
5 | 9 | 8 | 1 | 1 | -3 | 3 | 7 =(1+1+4+0+1) |
6 | |||||||
7 | |||||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | |||||||
12 |
Hi,
In your Matrix visual, i dragged Month from the Date Table to the Rows section. I wrote the folloing measure
YTD Var Abs = if(ISBLANK([Var Abs]),BLANK(),SUMX(CALCULATETABLE(SUMMARIZE(ALL('Date'[Month]),'Date'[Month],"ABCD",[Var Abs]),DATESBETWEEN('Date'[Date_key],DATE(YEAR(MAX('Date'[Date_key])),1,1),MAX('Date'[Date_key]))),[ABCD]))
Hope this helps.
Thanks. It works!!
You are welcome.
Also see the problem below:
2018 | not working | ||||||
Month | Actual | Fcst | Var | Var Abs | YTD Var | YTD Var Abs | YTD Var Abs I expect to be |
1 | 5 | 6 | -1 | 1 | -1 | 1 | 1=1 |
2 | 6 | 5 | 1 | 1 | 0 | 0 | 2 =(1+1) |
3 | 4 | 8 | -4 | 4 | -4 | 4 | 6 =(1+1+4 ) |
4 | 10 | 10 | 0 | 0 | -4 | 4 | 6 =(1+1+4+0) |
5 | 9 | 8 | 1 | 1 | -3 | 3 | 7 =(1+1+4+0+1) |
6 | |||||||
7 | |||||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | |||||||
12 |
Not entirely sure about this as I haven't actually replicated it but have you tried changing your "Var abs" measure to something like
=(Actual-Fcst)*if( (Actual-Fcst) <0,-1,1)
?
Unfortunately, it is not working either. For example, I want to calculate YTD ( Jan to Mar ). The correct answer is
Abs( Actual1 - Fcst1) + Abs (Actual 2 - Fcst 2 ) + Abs(Actual 3 - Fcst3)
But it results in the followng incorrect answer:
Abs [ (Actual1 +Actual2 + Actual 3) - (Fcst1+Fcst2+Fcst3) ]
Any thoughts ? 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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |