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, the February average should calculate as 10 in the sample data because there is a group, and January should calculate as 10.83 ((21+3+21+4+2+14)/6). 

 

ORDER_NUMBERORDER_GROUP_IDSKID_COUNTSHIP_DATE
801676 211/1/2022
801762 31/1/2022
801772 211/1/2022
801782 41/1/2022
801836 21/1/2022
80169780169741/1/2022
80173680169751/1/2022
80178080169751/1/2022
80178780178742/2/2022
80179980178742/2/2022
80181580178722/2/2022
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

2 Replies

  • 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