Forum Discussion

slatka11's avatar
slatka11
Icon for Helper I rankHelper I
3 years ago
Solved

Conditional average by month

I am looking to write a DAX to calculate the average skid count by month but the average should take the sum of the skids in an order group if there is one. Below is the sample data.    For example...
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    Here's a measure expression that shows one way to do it.

     

    Avg Skid_Count =
//var NoGroupSum = CALCULATE(SUM(T6[SKID_COUNT]), ISBLANK(T6[ORDER_GROUP_ID]))
VAR NoGroupCount =
    CALCULATE ( COUNT ( T6[SKID_COUNT] ), ISBLANK ( T6[ORDER_GROUP_ID] ) )
VAR OrderGroupCount =
    DISTINCTCOUNTNOBLANK ( T6[ORDER_GROUP_ID] )
VAR TotalSkidCount =
    SUM ( T6[SKID_COUNT] )
RETURN
    DIVIDE ( TotalSkidCount, NoGroupCount + OrderGroupCount )

     

     

    Pat

ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

Here's a measure expression that shows one way to do it.

 

Avg Skid_Count =
//var NoGroupSum = CALCULATE(SUM(T6[SKID_COUNT]), ISBLANK(T6[ORDER_GROUP_ID]))
VAR NoGroupCount =
    CALCULATE ( COUNT ( T6[SKID_COUNT] ), ISBLANK ( T6[ORDER_GROUP_ID] ) )
VAR OrderGroupCount =
    DISTINCTCOUNTNOBLANK ( T6[ORDER_GROUP_ID] )
VAR TotalSkidCount =
    SUM ( T6[SKID_COUNT] )
RETURN
    DIVIDE ( TotalSkidCount, NoGroupCount + OrderGroupCount )

 

 

Pat

slatka11's avatar
slatka11
Icon for Helper I rankHelper I
3 years ago

Thank you Pat, this works!