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.
Hi
I have month start snapshot data going back to 1st April 2021 for employees in post. Anonymised examples below......
You can see that one head (EE_Ref) can have more than one role (Role_Ref), and that each head and role combination has a row entry against each month start snapshot (reporting date), as long as they were employed at least part way through the previous month.
All dates in the table have an INactive relationship with the date field in a date table:
I have created the following measure to provide the distinct number of heads at snapshot date.
@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
If that doesn't help, probably need sample data as text.
Hi Greg, link to file below. Same data numbers, just anonymised.
I think having read your blog article that this is along the lines of what I am needing but have struggled to apply the logic to my data.
As mentioned previously, I am stuck with creating an additional measure that cumulatively sums the measure used to create the Active Staff at Snapshot matrix values, leading to something like this......
I would then look to divide each value by FINMONTHNUMBER to obtain an average FYTD. Thanks in advance.
If you have an index column per month-year then you can probably do something like this:
CALCULATE(
SUM(yourMeasure),
FILTER(
ALLSELECTED(monthyear),
ISONORAFTER(monthyear, MAX(monthyear), DESC)
)
)
/
SELECTEDVALUE( yourIndex )
Hope this helps!
Hi, apologies if I am being a bit thick (!) but the sum function requires a column rather than a measure. What am I doing wrong? I get where you are coming from re an Index and was planning to use financial month number in my date table as the denominator in the calculation. 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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |