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.
Hello,
I have a table of data that looks like this
House | ResidentNumbersMTD | Period |
GR | 9 | 30/6/2020 |
BR | 7 | 30/6/2020 |
AL | 7.8 | 30/6/2020 |
VS | 8 | 30/6/2020 |
RB | 5.5 | 30/6/2020 |
CR | 7 | 30/6/2020 |
CL | 7.5 | 30/6/2020 |
FL | 9.7 | 30/6/2020 |
EL | 8.7 | 30/6/2020 |
IR | 6.3 | 30/6/2020 |
CS | 1.9 | 30/6/2020 |
KL | 5.3 | 31/5/2020 |
GR | 9 | 31/5/2020 |
BR | 7 | 31/5/2020 |
AL | 8 | 31/5/2020 |
VS | 8 | 31/5/2020 |
RB | 4 | 31/5/2020 |
CR | 7 | 31/5/2020 |
CL | 8 | 31/5/2020 |
FL | 9.5 | 31/5/2020 |
EL | 9 | 31/5/2020 |
IR | 7.3 | 31/5/2020 |
I want to calculate the YTD average Number of residents. When house filter applied, it should be just the average number of residents for this house, however, when no house filter applied, I want Power Bi to calculate the sum of averages for all houses.
The formula I tried is:
YTD_No of Residents (MTD) =
VAR AvgMonth =
CALCULATE (
AVERAGEX ( Manual_Data, [No of Residents (MTD)] ),
ALL ( 'Calendar' ),
DATESYTD ( 'Calendar'[Date], "06-30" )
)
VAR SumTable =
SUMMARIZE (
Manual_Data,
Manual_Data[House],
"SumTableAvg",
CALCULATE (
AVERAGE ( Manual_Data[ResidentNumbersMTD] ),
ALL ( Manual_Data[Period] ),
DATESYTD ( Manual_Data[Period], "30-6" )
)
)
RETURN
IF (
HASONEFILTER ( Houses[ShortName] ),
AvgMonth,
SUMX ( SumTable, [SumTableAvg] )
)
It works ok when house filter is applied, however, when no house is selected, I get a total average for one month only (that is selected in another slicer).
Please help
Solved! Go to Solution.
[Your Measure] =
SUMX(
SUMMARIZE(
Manual_Data,
Houses[ShortName]
),
CALCULATE(
AVERAGE( Manual_Data[ResidentNumbersMTD] ),
// You might need to remove this ALLEXCEPT
// if you want the measure to respond
// to other filters.
ALLEXCEPT( Houses[ShortName] ),
DATESYTD(
// Calendar must be the date table
// marked as such in the model
// for this to work correctly.
'Calendar'[Date],
"06-30"
)
)
)
Please note that the above is written for a model where:
All relationships are standard Dim 1-one-way-* Fact. Slicing in a star schema as the above should be done only through dimensions. Then the above will work the way it's intended to.
Another perspective on the same issue,
Here is the table I have got bu using below formula. Line numbers are correct, but the total is wrong. It should be 83.31, not 88.46. Total must be a sum of lines
Formula I use is
xxx =
VAR one =
SUMMARIZE (
Manual_Data,
Manual_Data[House],
"SumTableAvg",
CALCULATE (
AVERAGE ( Manual_Data[ResidentNumbersMTD] ),
ALL ( Manual_Data ),
DATESYTD ( 'Calendar'[Date], "06-30" )
)
)
VAR two =
SUMX ( one, [SumTableAvg] )
RETURN
IF ( HASONEFILTER ( Houses[ShortName] ), two, SUMX ( one, [SumTableAvg] ) )
[Your Measure] =
SUMX(
SUMMARIZE(
Manual_Data,
Houses[ShortName]
),
CALCULATE(
AVERAGE( Manual_Data[ResidentNumbersMTD] ),
// You might need to remove this ALLEXCEPT
// if you want the measure to respond
// to other filters.
ALLEXCEPT( Houses[ShortName] ),
DATESYTD(
// Calendar must be the date table
// marked as such in the model
// for this to work correctly.
'Calendar'[Date],
"06-30"
)
)
)
Please note that the above is written for a model where:
All relationships are standard Dim 1-one-way-* Fact. Slicing in a star schema as the above should be done only through dimensions. Then the above will work the way it's intended to.
Thank you, this worked like magic
@Metricbits 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.
Thanks for the reply, but this doesn't solve my problem, unfortunately 😞
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |