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
Anonymous
Not applicable

Average from filtered aggregate with blanks

Hi, I am trying to calculate the average total [Cost] per [Code] per [Name], while ignoring blank [Cost] values. 

 

Here is an example of my goal:

 

I want to Sum [Cost] when [Code] equals "1" for "Brett", "Justin", and "Alex" individually, then take an average of the total. However, if one of the [Name] categories does not have a cost for the [Code], I do not want to include the [Name] category in the avaergae (thus: divide by 2, not 3). 

 

 

 Brett Code 1 Cost = 10+15+10 = 35

 Justin Code 1 Cost = 10 + 10 = 20

 Alex Code 1 Cost = blank (Thus, I do not want to take the average of 3 items, only 2)

Average total [Cost] for [Code] = "1" = (35+20)/2 = 27.5

 

However, because "Alex" has [Cost] for [Code] = 2, this average would be divided by 3.

 

Thank you for taking the time to read my question, I'll certainly give thumbs up and an accepted solution. 

 

NameCodeCost
Brett110
Brett115
Brett110
Brett1 
Brett210
Brett212
Brett2 
Justin110
Justin110
Justin25
Justin210
Justin2 
Justin2 
Justin2 
Alex215
Alex220
Alex2 
Alex2 
1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Anonymous,

 

Try these measures:

 

Sum Cost = SUM ( Table1[Cost] )

Average Cost =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, Table1[Name], Table1[Code] ),
        "@Cost", [Sum Cost]
    )
VAR vResult =
    AVERAGEX ( vTable, [@Cost] )
RETURN
    vResult

 

DataInsights_0-1625082963021.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

@Anonymous,

 

Try these measures:

 

Sum Cost = SUM ( Table1[Cost] )

Average Cost =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, Table1[Name], Table1[Code] ),
        "@Cost", [Sum Cost]
    )
VAR vResult =
    AVERAGEX ( vTable, [@Cost] )
RETURN
    vResult

 

DataInsights_0-1625082963021.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.