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.
Dear Power BI user
I have set up a table in power BI which looks similar to the example table below:
Week Commencing Count Of Additions Care Group Clinician Moving Range
01/01/2020 3 A 1
07/01/2020 6 A 1 3
14/01/2020 4 A 1 2
01/01/2020 9 B 2 5
07/01/2020 8 B 2
14/01/2020 7 B 2
01/01/2020 9 A 3
07/01/2020 8 A 3
14/01/2020 7 A 3
I initially created 2 new index columns called "Index" starting from 0 and and Index.1" starting from 1.
The calculation below only works at the most granular level when looking at clinician. I think it only works at clinician level as the index fields are always looking at the row above to calculate the difference in count of wl additions each row
I.e.
I was wondering if someone could help me understand how I can get my calculation to work with the index columns so that the moving range works at different levels of hiercahy in the table I.e. works out the moving range at Care Group Level
Kind Regards
Helen
Hi @helen_p no need to do the INDEX you just need to add the following measure:
Moving_Average =
VAR temp_table =
FILTER (
ALL ( 'Waiting List Extract'[Week Commencing] );
'Waiting List Extract'[Week Commencing]
< SELECTEDVALUE ( 'Waiting List Extract'[Week Commencing] )
)
VAR Previous_Date =
MAXX ( temp_table; 'Waiting List Extract'[Week Commencing] )
RETURN
ABS (
SUM ( 'Waiting List Extract'[Count of Addtions] )
- CALCULATE (
SUM ( 'Waiting List Extract'[Count of Addtions] );
FILTER (
ALL (
'Waiting List Extract'[Care Group];
'Waiting List Extract'[Clinician];
'Waiting List Extract'[Week Commencing];
'Waiting List Extract'[Count of Addtions]
);
'Waiting List Extract'[Week Commencing] = Previous_Date
&& 'Waiting List Extract'[Care Group]
IN VALUES ( 'Waiting List Extract'[Care Group] )
&& 'Waiting List Extract'[Clinician]
IN VALUES ( 'Waiting List Extract'[Clinician] )
)
)
)
This will pickup the previuos week date and then based on the aggregation make the calculation:
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |