cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Take the average of values based on a certain slicer, then sum the values

Hi all,

I have situation where i need to create a specific measure (or a combination of measures) where i need to take the average of a certain column based on the date filter and then sum those values based on a different slicer with based on certain categories.

for example: i would like to grab the average of the month January which is from category 1 which is 8,95 and the average of category 2 for january 5,45. and then sum 8,95 and 5,45 together, which is 14,4. only the month/year and the category that need to be used for the calucation are decided by the report user in a slicer and can not be hardcoded.

any help is appreciated.

here is my sample data:  both joins are 1 to many

 category_id date value 1 1-1-2024 9,6 2 1-1-2024 6,7 3 1-1-2024 2,1 1 4-1-2024 8,3 2 4-1-2024 4,2 3 4-1-2024 6,1 1 1-2-2024 3,5 2 1-2-2024 6,8 3 1-2-2024 3,34 1 3-2-2024 4,2 2 3-2-2024 2,1 3 3-2-2024 2,6

 id category 1 category 1 2 category 2 3 category 3

 date month year 1-1-2024 January 2024 4-1-2024 January 2024 1-2-2024 February 2024 3-2-2024 February 2024
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have create a simple sample, please refer to my pbix file to see if it helps you.

Create 2 measures.

``````Measure =
VAR _table2 =
CALCULATE (
MAX ( Table2[category] ),
FILTER ( ALL ( Table2 ), Table2[category] = SELECTEDVALUE ( Table2[category] ) )
)
VAR _month =
SELECTEDVALUE ( 'Table 3'[month ] )
VAR _ta2 =
CALCULATE (
MAX ( Table2[id] ),
FILTER (
ALL ( Table2 ),
Table2[id] = SELECTEDVALUE ( Table2[id] )
&& Table2[category] = _table2
)
)
VAR _date =
CALCULATE (
MAX ( 'Table 3'[date] ),
FILTER ( ALL ( 'Table 3' ), 'Table 3'[month ] = _month )
)
VAR _mindate =
CALCULATE (
MIN ( 'Table 3'[date] ),
FILTER ( ALL ( 'Table 3' ), 'Table 3'[month ] = _month )
)
RETURN
CALCULATE (
AVERAGE ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[category_id] = _ta2
&& 'Table'[date] >= _mindate
&& 'Table'[date] <= _date
)
)
``````
``````Measure2 =
VAR _q = [Measure]
VAR _b =
SUMMARIZE ( 'Table', 'Table'[category_id], "aaa", [Measure] )
RETURN
IF ( HASONEVALUE ( 'Table'[category_id] ), _q, SUMX ( _b, [aaa] ) )
``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Anonymous
Not applicable

I have create a simple sample, please refer to my pbix file to see if it helps you.

Create 2 measures.

``````Measure =
VAR _table2 =
CALCULATE (
MAX ( Table2[category] ),
FILTER ( ALL ( Table2 ), Table2[category] = SELECTEDVALUE ( Table2[category] ) )
)
VAR _month =
SELECTEDVALUE ( 'Table 3'[month ] )
VAR _ta2 =
CALCULATE (
MAX ( Table2[id] ),
FILTER (
ALL ( Table2 ),
Table2[id] = SELECTEDVALUE ( Table2[id] )
&& Table2[category] = _table2
)
)
VAR _date =
CALCULATE (
MAX ( 'Table 3'[date] ),
FILTER ( ALL ( 'Table 3' ), 'Table 3'[month ] = _month )
)
VAR _mindate =
CALCULATE (
MIN ( 'Table 3'[date] ),
FILTER ( ALL ( 'Table 3' ), 'Table 3'[month ] = _month )
)
RETURN
CALCULATE (
AVERAGE ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[category_id] = _ta2
&& 'Table'[date] >= _mindate
&& 'Table'[date] <= _date
)
)
``````
``````Measure2 =
VAR _q = [Measure]
VAR _b =
SUMMARIZE ( 'Table', 'Table'[category_id], "aaa", [Measure] )
RETURN
IF ( HASONEVALUE ( 'Table'[category_id] ), _q, SUMX ( _b, [aaa] ) )
``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi @Anonymous ,

thank you for the reply. This indeed helped me get to a working result.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors