How to calculate a median across measures

[rodcp]

Hello everyone,

I am struggling with this issue... let me explain to you.

I have 4 measures in a table, each measure represent the performance of an array of solar panels.

What I need to calculate is the Median between these four measures.  But the median function seems not to work between measures. 

My second aproach... I created a calculated colum in the table, but still the Median function seems to work only vertically (between rown) and not horizontally (between colums).

Let me give you some examples on the data...

This is my main table, it is named "cn1_limpieza".

As you can see the data is recorder every 5 minutes.  The fields cur01, cur02, cur03, represent the string's current of a solar PV Array.

I have intermediate measures, for example

[SumaCur1] = SUM(cn1_limpieza[cur01] and

[SumaCur2] = SUM(cn1_limpieza[cur02]). and so on...  

The following are the indicators I am using, they represents the % that any of the string performed with respect to the String1 minus some offsets that have been define in another table.

What I am plotting right now is the following.

Where the X axis is the field "ct" in the main table.

What I need to calculate is the Median between the measurements per "ct". So that in the previous graph you can see only one value per "ct", which will be the Median of them.

Thanks a lot for your help 

Kind regards.

[dedelman_clng]

You might want to try unpivoting the table and turning the 5 measurement columns into 2 columns (Measurement No and Value).  Then you can use MEDIAN on the value column.

For example

TS    CT    CN1   CUR01   CUR02   CUR03   CUR04   CUR05
xx    17    7      7.33   7.27    7.09    7.23    7.05

becomes

TS   CT   CN1   CurNo   Value
xx   17   7     1       7.33
xx   17   7     2       7.27
xx   17   7     3       7.09
xx   17   7     4       7.23
xx   17   7     5       7.05

In Query Editor go to Transform tab, highlight the 5 CURxx columns and click Unpivot Columns

Hope this helps

David

[rodcp]

Hello dedelman thanks you very much for your reply.

I did what you recomend and now the data is unpivoted.

Whant I don't know now is how to calculate the Median of the daly Sum for each current.

I am guessing that you have to use the Calculate DAX function inside MedianX function.  

For example:

I first want to compute the daly SUM for each current (cur01, cur02, ... , cur 05)

Thank I want to calculate the Median between these Daily SUMs

(Actually is a bit more complicated than that, but if I can do this, then I have all is needed to make the real calculation)

Thanks a lot for your help.

[dedelman_clng]

You may want to look at creating summary tables, 1 for all of the daily sums and one for the MEDIAN values of those sums per day.  Others may be able to elaborate on a way to do this strictly with measures

SumTab =
SUMMARIZE (
    cn1_limpieza,
    cn1_limpieza[tstamp],
    cn1_limpieza[ct],
    cn1_limpieza[cn1],
    cn1_limpieza[CurrNo],
    "DailySumPerCurrent", SUM ( cn1_limpieza[Value] )
)

//Median across all CurrNo per day
MedianTab =
SUMMARIZE (
    SumTab,
    SumTab[tstamp],
    SumTab[ct],
    SumTab[cn1],
    "DailyMedian", MEDIAN ( SumTab[DailySumPerCurrent] )
)

You may be able to use measures like I said depending on your visualizations.  Hope this gives you an idea on how to move forward.

David