Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gvg
Post Prodigy
Post Prodigy

Average calculation by criteria

Hi,

 

I would like to calculate average of Amount by some specific column in the same table. Thought this was simple, but I get strange results with this measure, which, I would expect, should do the job:

 

Calculated Average = CALCULATE(AVERAGE(Table1[Amount]),Table1[DocNo])

This is my source table and the result. Why the measure refuses to calculate average on DocNo, and instead arbitrarily calculates average on Manager?

 

Untitled.jpg

I am expecting Calculated Average to be like this regardless of Manager field:

 

Manager   DocNo       Calculated Average

John          322            35

John          566            20

Peter         123            10

Sam           322            35

-----------------------------

Total:                           21.7      

 

21.7 being  (35 + 20 + 10) / 3. Standard Quick Measure comes up with 33 in this case.

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

Hi @gvg

 

Try this MEASURE

 

Calculated_Average =
IF (
    HASONEFILTER ( Table1[DocNo] ),
    CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ),
    AVERAGEX (
        VALUES ( Table1[DocNo] ),
        CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

Hi @gvg

 

Try this MEASURE

 

Calculated_Average =
IF (
    HASONEFILTER ( Table1[DocNo] ),
    CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) ),
    AVERAGEX (
        VALUES ( Table1[DocNo] ),
        CALCULATE ( AVERAGE ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[DocNo] ) )
    )
)

Regards
Zubair

Please try my custom visuals

Yep, this works like charm. Thank you !

@gvg

 

Actually your MEASURE is equivalent to

 

CalculatedAverage = CALCULATE(AVERAGE(Table1[Amount]),ALL(Table1[DocNo]))

The Calculate Expression

 

CALCULATE (
    <expression>,
    table[column] = <value>
)

is internally transformed into

 

CALCULATE (
    <expression>, 
    FILTER (
        ALL ( table[column] ),
        table[column] = <value> 
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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