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

[BrianIsTaken]

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

[v-rongtiep-msft · Accepted solution]

Hi @BrianIsTaken ,

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] ) )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

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.

[BrianIsTaken]

Hi @v-rongtiep-msft ,

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