Compare individual value with median

[Anonymous]

Hi everyone,

I believe I will need a push in the right direction to calculate, compare and summarize the output of several generators per time period.

Raw Data

Date	Project	Device No	Output
25-Sep	Sydney	1	2310
25-Sep	Sydney	2	2400
25-Sep	Sydney	3	2430
25-Sep	Sydney	4	2390
25-Sep	Sydney	5	2420
25-Sep	Sydney	6	2380
25-Sep	Brisbane	1	1860
25-Sep	Brisbane	2	1890
25-Sep	Brisbane	3	1730
25-Sep	Brisbane	4	1830
26-Sep	Sydney	1	2100
26-Sep	Sydney	2	2110
26-Sep	Sydney	3	2080
26-Sep	Sydney	4	2090
26-Sep	Sydney	5	2100
26-Sep	Sydney	6	2120
26-Sep	Brisbane	1	1600
26-Sep	Brisbane	2	1610
26-Sep	Brisbane	3	1680
26-Sep	Brisbane	4	1660

Step 1

For each time period (in this example day) I want to compare the output of each generator with the median of all generators at that location.

If the individual output is above the median, I deem that generator as fully available (=1). If below, it is deemed as not available (=0).

Date	Project	Device No	Output	Median per project per day	Above Median
25-Sep	Sydney	1	2310	2395	0
25-Sep	Sydney	2	2400	2395	1
25-Sep	Sydney	3	2430	2395	1
25-Sep	Sydney	4	2390	2395	0
25-Sep	Sydney	5	2420	2395	1
25-Sep	Sydney	6	2380	2395	0
25-Sep	Brisbane	1	1860	1845	1
25-Sep	Brisbane	2	1890	1845	1
25-Sep	Brisbane	3	1730	1845	0
25-Sep	Brisbane	4	1830	1845	0
26-Sep	Sydney	1	2100	2100	1
26-Sep	Sydney	2	2110	2100	1
26-Sep	Sydney	3	2080	2100	0
26-Sep	Sydney	4	2090	2100	0
26-Sep	Sydney	5	2100	2100	1
26-Sep	Sydney	6	2120	2100	1
26-Sep	Brisbane	1	1600	1635	0
26-Sep	Brisbane	2	1610	1635	0
26-Sep	Brisbane	3	1680	1635	1
26-Sep	Brisbane	4	1660	1635	1

Step 2

Now I need to sum up these numbers.

Project	Device No	Sum of Above Median
Sydney	1	1
Sydney	2	2
Sydney	3	1
Sydney	4	0
Sydney	5	2
Sydney	6	1
Brisbane	1	1
Brisbane	2	1
Brisbane	3	1
Brisbane	4	1

In PowerBI I have calculated the median as follwoing: 

MedianPerProjectPerDay = CALCULATE(median(Output[Output]),ALL(Output[DeviceNo]))

And compare the individual with the following measure:

Sumx = sumx(Output,if(Output[Output]>[MedianPerProjectPerDay],1,0))

Somehow my sumx measure median does not work across several rows.

Excel file with final result: https://www.dropbox.com/s/ru33h3wr2vkr6g5/CompareWMedian.xlsx?dl=0

PBIX with my approach: https://www.dropbox.com/s/qy1e4jm5ikc9byj/CompareWMedian.pbix?dl=0

Many thanks upfront.

Regards

Andreas

[stevedep · Accepted solution]

Hi there @Anonymous ,

This is what I have for you:

__ComparewithMedian = 
SUMX(SUMMARIZE('Table','Table'[Date],'Table'[Device No], "c",
IF(SUM('Table'[Output]) > 
CALCULATE(MEDIANX('Table', 'Table'[Output]), ALLEXCEPT('Table','Table'[Project], 'Table'[Date])), 1, 0)),[c])

As seen here:

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve.  

[stevedep · Accepted solution]

Hi there @Anonymous ,

This is what I have for you:

__ComparewithMedian = 
SUMX(SUMMARIZE('Table','Table'[Date],'Table'[Device No], "c",
IF(SUM('Table'[Output]) > 
CALCULATE(MEDIANX('Table', 'Table'[Output]), ALLEXCEPT('Table','Table'[Project], 'Table'[Date])), 1, 0)),[c])

As seen here:

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve.  

[Anonymous · Accepted solution]

Awesome, Thanks @stevedep . Much appreciate your help.

[amitchandak]

@Anonymous , You need values on some group by to make it work

Sumx = sumx(values(Output[project]) ,if(Sum(Output[Output])>[MedianPerProjectPerDay],1,0))

or create median as column not measure

[Anonymous]

Thanks @amitchandak ,

I tried this and a few other ideas, but am still stuck. Your provided measeure sums up the output and then compares it with the median. Therefore I will always only get 1 as maximum. I need to compare the individual output value with the median for that period, determine if it is 0 or 1 and then sum up all the 1s.

I also tried to do it via calculated column as 

CalColMedian = CALCULATE(MEDIAN(Output[Output]),ALL(Output[DeviceNo]))

Which gives me te following error message: "Expressions that yield variant data-type cannot be used to define calculated columns."

Just to double check, I used AVERAGE, which seems to produce a result. But it seems the calculation is limited to the values within a row

CalColMedian = CALCULATE(AVERAGE(Output[Output]),ALL(Output[DeviceNo]))

Next I tried to create a calculated table with the aim to use lookupvalue later.

CalTabSummarize = SUMMARIZE(Output,Output[Project],Output[Date].[Date],"MedianDayProject_tab",MEDIAN(Output[Output]))

Which gives me the following error: "The DAX expression for calculated table 'CalTabSummarize' results in a variant data type for column 'MedianDayProject_tab'. Please modify the calculation such that the column has a consistent data type."

Replacing it with Average works again

CalTabSummarize = SUMMARIZE(Output,Output[Project],Output[Date].[Date],"MedianDayProject_tab",AVERAGE(Output[Output]))

After a lot of trying I am still lost...