Forum Discussion

slatka11's avatar
slatka11
Helper 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
Solution 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
    Helper I
    3 years ago

    Thank you Pat, this works!