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 am trying to set up a running sum across two dimensions:
1. A specified number of weeks (1 in the example shown below)
2. Year (3 in the example shown below)
Both the years required and the number of weeks are selected using a drop down filter.
When viewed in a table containing both the WeekNo and CumulWeekNo the right rows and values are picked out.
(please see left hand table - for example: WeekNo = 2: values for 3 selected years are shown). I want to sum these and show the answer by WeekNo.
When viewing a table containing only WeekNo, instead of seeing the sum of the years selected, I only see the value for the latest year (see the left Table, below). In the example: for WeekNo = 2 I want to see the answer 9+8+12 = 29 instead of 12 (which is the answer for the latest CumulWeekNo).
The code I am using is:
NoNewSel_Weeks2 = Calculate(sumx('Conversion',[NoNew]),
filter(ALLEXCEPT('Conversion',Conversion[CategoryDesc],Conversion[SubCategoryDesc]),
'Conversion'[CumulWeekNo]<=MAX('Conversion'[CumulWeekNo]) &&
'Conversion'[CumulWeekNo]>(Max('Conversion'[CumulWeekNo])-
SELECTEDVALUE(New_AverageOverWeeks[NoWeeks]))))
Very many thnaks in advance
Stephen
Solved! Go to Solution.
In your formula, you use MAX('Conversion'[CumulWeekNo]) to specify the range in filter. If you don't put the 'Conversion'[CumulWeekNo] column in your visual, it will always get the max CumulWeekNo under each WeekNo. This is the reason why it always get the latest year result.
To get your expected result, you should write your formula like below:
NoNewSel_Weeks_final = SUMX ( SUMMARIZE ( 'Conversion', 'Conversion'[Year], "NoNewSel_Weeks3", [NoNewSel_Weeks2] ), [NoNewSel_Weeks3] )
See my sample below:
Regards,
That works perfetly. Very very many thanks.
I have been trying to work out how to do this for a long time.
Best regards
Stephen
In your formula, you use MAX('Conversion'[CumulWeekNo]) to specify the range in filter. If you don't put the 'Conversion'[CumulWeekNo] column in your visual, it will always get the max CumulWeekNo under each WeekNo. This is the reason why it always get the latest year result.
To get your expected result, you should write your formula like below:
NoNewSel_Weeks_final = SUMX ( SUMMARIZE ( 'Conversion', 'Conversion'[Year], "NoNewSel_Weeks3", [NoNewSel_Weeks2] ), [NoNewSel_Weeks3] )
See my sample below:
Regards,
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |