Average per row by date

[Boris_EmV]

Hi,

I have this sample data:

Date	Patient	TreatID	COGS
1/1/2023	A	1	1100
1/3/2023	A	1	1100
1/5/2023	A	1	1100
1/8/2023	A	1	1100
1/10/2023	A	1	1100
1/11/2023	A	1	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.

[v-heq-msft · Accepted solution]

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

[v-heq-msft · Accepted solution]

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]

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]

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 · Accepted solution]

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 ) )