Forum Discussion

Creative_tree88's avatar
Creative_tree88
Icon for Helper V rankHelper V
3 years ago
Solved

Median based on multiple conditions

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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