Forum Discussion

Boris_EmV's avatar
Boris_EmV
Frequent Visitor
2 years ago
Solved

Average per row by date

Hi,

I have this sample data:

DatePatientTreatIDCOGS
1/1/2023A11100
1/3/2023A11100
1/5/2023A11100
1/8/2023A11100
1/10/2023A11100
1/11/2023A1

1100

 

I would like to create a measure to calculate Average COGS per row. If I select for example 1/10/2023 I want to see 1100 / 6 (count of dates) or 183.3 in this row.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Boris_EmV ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    Create a measure

    Average = 
VAR _COUNT = COUNTROWS(ALL('Table'))
RETURN
    IF(
        ISFILTERED('Table'[COGS]),
        SELECTEDVALUE('Table'[COGS])/_COUNT,
        SELECTEDVALUE('Table'[COGS])
    )

    Final output

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

  • timalbers's avatar
    timalbers
    2 years ago

    Hi Boris_EmV 
    sorry, my bad, I made a mistake. It should of course be 

    CALCULATE( COUNTROWS( Table ), ALL( Table ) )

    While you could also shorten it to this:

    COUNTROWS( ALL( Table ) )

     

4 Replies

  • timalbers's avatar
    timalbers
    Icon for Skilled Sharer rankSkilled Sharer
    2 years ago

    Hi Boris_EmV 


    try something like this:

    _measure =
    VAR nr_of_dates = CALCULATE( COUNTROWS( Table[Date] ), ALL( Table ) )
    VAR avg_cogs = SUM( Table[COGS] ) / nr_of_dates
    RETURN avg_cogs

     

    Cheers

    Tim

    • Boris_EmV's avatar
      Boris_EmV
      Frequent Visitor
      2 years ago

      Thanks. I've tried similar measure like:

      _measure =
    VAR nr_of_dates = CALCULATE( COUNT( Table[Date] ), ALL( Table[Date]) )
    VAR avg_cogs = SUM( Table[COGS] ) / nr_of_dates
    RETURN avg_cogs

       

      It is showing correct values 183.3 by row and total is correct, but when I use slicer for dates (slicer is my calendar dimention connected to the table) is showing again 1100 at this date (10 Oct) for example. 

      • timalbers's avatar
        timalbers
        Icon for Skilled Sharer rankSkilled Sharer
        2 years ago

        Hi Boris_EmV 
        sorry, my bad, I made a mistake. It should of course be 

        CALCULATE( COUNTROWS( Table ), ALL( Table ) )

        While you could also shorten it to this:

        COUNTROWS( ALL( Table ) )

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Boris_EmV ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    Create a measure

    Average = 
VAR _COUNT = COUNTROWS(ALL('Table'))
RETURN
    IF(
        ISFILTERED('Table'[COGS]),
        SELECTEDVALUE('Table'[COGS])/_COUNT,
        SELECTEDVALUE('Table'[COGS])
    )

    Final output

     

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly