Median based on multiple conditions

[Creative_tree88]

Hi - Im trying, but failing on this one...I need to calculate median values based on a field (Req to Att) but I need it to look only at Current Week (identifed as column 'Period') and also a specific report name called 'Reporting'.  

I then need to calculate the difference between current week and previous week, and also current week to 6 months ago.

Bit confused...I've attached a sample file with the sort of answer I'm looking to show in a table, all together, in Power BI.

Many thanks in advance for your help.

Median Sample Data 

Kind regards

[v-cgao-msft · Accepted solution]

Hi @Creative_tree88 ,

First you need a table like:

relationships:

then please create these measures:

Attend = CALCULATE(MEDIAN('Table'[Req to Att]),'Table'[Type]="Attend")

Attend' Change = 
VAR _index = MAX('Table2'[Index])
VAR _pre = CALCULATE([Attend],FILTER(ALLSELECTED('Table2'),'Table2'[Index]=_index-1))
VAR _change = [Attend] - _pre
RETURN
IF(_pre<>BLANK(),_change)

Reporting = CALCULATE(MEDIAN('Table'[Req to Att]),'Table'[Type]="Reporting")

Reporting' Change = 
VAR _index = MAX('Table2'[Index])
VAR _pre = CALCULATE([Reporting],FILTER(ALLSELECTED('Table2'),'Table2'[Index]=_index-1))
VAR _change = [Reporting] - _pre
RETURN
IF(_pre<>BLANK(),_change)

result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-cgao-msft · Accepted solution]

Hi @Creative_tree88 ,

First you need a table like:

relationships:

then please create these measures:

Attend = CALCULATE(MEDIAN('Table'[Req to Att]),'Table'[Type]="Attend")

Attend' Change = 
VAR _index = MAX('Table2'[Index])
VAR _pre = CALCULATE([Attend],FILTER(ALLSELECTED('Table2'),'Table2'[Index]=_index-1))
VAR _change = [Attend] - _pre
RETURN
IF(_pre<>BLANK(),_change)

Reporting = CALCULATE(MEDIAN('Table'[Req to Att]),'Table'[Type]="Reporting")

Reporting' Change = 
VAR _index = MAX('Table2'[Index])
VAR _pre = CALCULATE([Reporting],FILTER(ALLSELECTED('Table2'),'Table2'[Index]=_index-1))
VAR _change = [Reporting] - _pre
RETURN
IF(_pre<>BLANK(),_change)

result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Creative_tree88 · Accepted solution]

@v-cgao-msft - many thanks indeed!