Measure: Median of value evaluated within day

[jeyare]

Hi there,

I have a problem finding a solution how to (in Measure) Calculate a Daily MEDIAN for values, based on this setup:

- I have column DATE (date)

- I have column FAILURES (Integer)

- I have column SERIAL_NO (String)

where

One day from the DATE has several failures generated by the SERIAL_NO .... then I have in the DATE column several same dates for each the Failures.

I can calculate MEDIAN for Non-zero or Non-blank values from the FAILURES:

CALCULATE(
MEDIAN('Table'[failure]),'Table'[failure]<>0||'Table'[failure]<>BLANK()))

thx for a help

[jppv20]

Hi @jeyare ,

Can you give an example of the desired output you require?

Jori

[jeyare]

Hi @jppv20 ,

the example is attached in my screenshot from XLS.

Let start from a scratch:

- I have column DATE (date)

- I have column FAILURES (Integer)

What I need:

1. I need to Calculate how many failures happened on each particular day. For this purpose, I need to create a new column, for example "Sum of failures for the same date". This column will create similar to XLS: COUNTIFS(range of "date", same "date" in the row, range of "failures",1)
2. Then I need to create New Measure that will create the MEDIAN (defined by my script + tuned by you.

I hope, it is more clear.

Jan

[jeyare · Accepted solution]

I've got it:

First - for the SUM o ailures per day:

CALCULATE(

SUM('Table'[failure]),

FILTER(

ALL('Table') ,

'Table'[date] = EARLIER('Table'[date])

)

)

Then, next step:

CALCULATE(
MEDIAN('Table'[SUM o ailures per day]),'Table'[SUM o ailures per day]<>0||'Table'[SUM o ailures per day]<>BLANK()))

[jppv20]

Hi @jeyare ,

Think I understand better now. You can create the calculated column with the following formula:

failures/day = CALCULATE(SUM(Failures[failures]),ALLEXCEPT(Failures,Failures[date]))

After that you can create the measure:

Median = MEDIAN(Failures[failures/day])

Hope this helps!

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[jppv20]

Hi @jeyare ,

Try this:

CALCULATE(
MEDIAN('Table'[failure]),'Table'[failure]<>0||'Table'[failure]<>BLANK(),ALLEXCEPT('Table','Table'[date]))

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

[jeyare]

Thank you @jppv20 ,

maybe I was wrong in my description. Here is better situation scope:

Maybe I need to create a New Column: Sum of failures for the same date

here I need to Calculate Sum of Failures per each day separately (follow the table),

then I can Calculate the Median from the New Column.

I will appreciate a help. 

Thx